Question 113966 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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) || ' ' || split_part(replace(address, ', ', ''), ' ', 7) as ward
from mytable
    Login or Signup to reply.
  2. 0

    Using regexp_match:

    select (regexp_match(address, '(?<=Tows)d+'))[1] tow,
       (regexp_match(address, '(?<=Floors)d+'))[1] floor,
       (regexp_match(address, '(?<=Wards)[^,]+'))[1] ward
from tbl

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search