Search code examples
sqlinformix

Is there an easy method to tidy up a Database Column by separating the place and the post code?

I currently have this problem:
I own a database with different columns like name, adress, place and post code. Unfortunately I have some messy entries where the post code is empty but the place is built like this: PLACE POST CODE. Is there a way to clean this mess up? Or do I have to do it manually?

The database is built on Informix.

Sample Data:

Customer Number: 12315 (Auto Incremental)

Name : Best Machines (Company Name)

Other Names : Germany

Street Adress : Best Road in Town No. 15

Post Code : 51691

Name of Place : Best City HERE IS THE PROBLEM: Because some are saved like Best City POSTCODE even though there are two separate Columns ( i.e. Best City 51691)

Country : Best Country

Thanks in advance,

Gusdl

Solution

  • You could query the rows that matches "*" + POST CODE and update the place with PLACE - POST CODE

    This update makes this

    update name_of_table set place = substr(place, 1, length(place)-length(post_code))
 where place matches "*" || post_code

    I tested in Informix 12.10 and makes what you want