CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2378895
user2378895

Reputation: 431

oracle split comma delimited string into columns

I have a table like

Charge_num      mapping_col
---------       -----------
p1.pm.100       1.1.1,1000
p1.pm.110       1.2.1,1000
p1.pm.120       1.3.1,1000

I need to split the "mapping_col" into 2 columns like this:

Charge_num      wbs       obs
---------       ---       ---
p1.pm.100       1.1.1     1000
p1.pm.110       1.2.1     1000
p1.pm.120       1.3.1     1000

Upvotes: 2

Views: 10231

Answers (2)

kevinskio
kevinskio

Reputation: 4551

select charge_num, 
substr(mapping_col,1,instr(mapping_col,',',1)-1) AS first_half,
substr(mapping_col, instr(mapping_col,',',1)+1) as last_half
from your_table

Note that the practice of storing numbers as strings is prone to error. If you were to cast these results as NUMBER(9) it could break unpredictably if your data is irregular

Upvotes: 2

Boneist
Boneist

Reputation: 23578

REGEXP_SUBSTR to the rescue!

with sample_data as (select 'p1.pm.100' charge_num, '1.1.1,1000' mapping_col from dual union all
                     select 'p1.pm.110' charge_num, '1.2.1,1000' mapping_col from dual union all
                     select 'p1.pm.120' charge_num, '1.3.1,1000' mapping_col from dual)
select charge_num,
       regexp_substr(mapping_col, '[^,]+', 1, 1) wbs,
       regexp_substr(mapping_col, '[^,]+', 1, 2) obs
from   sample_data;

CHARGE_NUM WBS                            OBS                           
---------- ------------------------------ ------------------------------
p1.pm.100  1.1.1                          1000                          
p1.pm.110  1.2.1                          1000                          
p1.pm.120  1.3.1                          1000

Upvotes: 2

Related Questions