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

Question

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

Gordon Linoff · Accepted Answer · 2021-06-14 14:15:55Z

1

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.

answered Jun 14, 2021 at 14:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ankit Bajpai · Accepted Answer · 2021-06-14 14:17:04Z

1

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;

answered Jun 14, 2021 at 14:17

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related