Split string to get number and split split string into separate columns in PostgreSQL-CodePudding

I have I have a column address with the string like this:

Tow 10 Floor 223, Ward AA BB, District CC DD, City E F

and

Tow 110 Floor 23, Ward BB AA, District DD CC, City F E

...

(more than 10000 lines)

I want to split this string into separate columns, remove the characters after the 2nd comma and insert column with split value into my table.

Look like this:

Tow	Floor	Ward
10	223	AA BB
110	23	BB AA

CodePudding user response：

Use split_part(), which doesn't support regex so you'll have to nuke the commas:

select
     split_part(replace(address, ', ', ''), ' ', 2) as tow,
     split_part(replace(address, ', ', ''), ' ', 4) as floor,
     split_part(replace(address, ', ', ''), ' ', 6) as ward
from mytable

CodePudding user response：

Using regexp_match:

select (regexp_match(address, '(?<=Tow\s)\d '))[1] tow,
       (regexp_match(address, '(?<=Floor\s)\d '))[1] floor,
       (regexp_match(address, '(?<=Ward\s)[^,] '))[1] ward
from tbl

See fiddle.