CODEWITHSUNDEEP
sqloracle-databasesplit
Chronicles_X0
Chronicles_X0

Reputation: 13

Split a column into multiple columns based on comma (Oracle)

I'm creating a new table and carrying over several fields from a previous table. One of the fields is "Address" that needs to be split into several columns based on comma in the new table.

i.e current column

      Clientid         Address
      1                123 E 123th st, APT 4L
      2                17 E16th st, APT 3B

newly created columns:

       Clientid                address1                 address2
       1                       123 E123th st            APT 4L
       2                       17 E 16th st             APT 3B

My question is is this even possible without hardcoding? Since I can't tell how many characters away the comma is for each individual record I'm assuming I'll need to come up with some sort of loop to check the condition?

Thanks

Upvotes: 0

Views: 561

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

I think you need some basic SUBSTR & INSTR function -

SELECT Clientid,
       SUBSTR(Address, 1, INSTR(Address, ',') - 1) address1,
       SUBSTR(Address, INSTR(Address, ',') + 1, LENGTH(Address)) address2
  FROM YOUR_TABLE;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use regexp_substr(), probably with trim():

select trim(regexp_substr(address, '[^,]+', 1)),
       trim(regexp_substr(address, '[^,]+', 1, 2))

Here is a db<>fiddle.

Upvotes: 1

Related Questions