split the fields in a column into 3 columns

Applications Web Development
1

Have a column "address" which is combination of city, region and postal code like.

Format is : city<comma><space>region<space>postal code
abc, xyz 123456

All these three city, region and postal code are not mandatory. There can be any one of the above. In that case a nell needs to be inserted.

Need to split these fields into 3 columns city, region and postal code and insert into another table.

script can be in mysql or sqlserver

Thanks

2

mysql is not a script, it is a database.

Normally you use a language like PHP or PERL as the scripting language and that script calls a mysql function.

3

if you mean that in your table, you have a column named address, and you want to split data in it, then:

  1. alter the table and add three new columns for storing city, region, and postal code
  2. write a java/php/perl program to:
    (a) read data in address column by running a select query
    (b) split the data and prepare insert queries
    (c) execute insert queries to add data to the new columns

maybe there could be a better approach to do this