CODEWITHSUNDEEP
sqloracle-database
Doubts
Doubts

Reputation: 11

Split a column on a comma

I have oracle query that returns result like

Column1  Column2  Column3
  1        -A,D    -DEF
  2        -B,C    -JHI

How can i get the result like

Column1 Column2  Column3  Column4
  1       -A       -D      -DEF

Upvotes: 0

Views: 76

Answers (2)

TechDo
TechDo

Reputation: 18659

Please try:

select
  Column1, 
  substr(Column2 , 1, instr(Column2, ',')-1) as Column2, 
  substr(Column2 , instr(Column2, ',')+1) as Column3, 
  Column3 as Column4 
from(
      select  1 as Column1, '-A,D' as Column2, '-DEF' as Column3 from dual
)x

Upvotes: 2

Kevin Bowersox
Kevin Bowersox

Reputation: 94499

Utilize the SUBSTRING function to receive part of the String and the INSTR function to find the position of the comma.

select
    column1,
    substring(column2,1,instr(column2,',')-1) as column2a,
    substring(column2,instr(column2,',')+1) as column2b,
    column3
from mytable

Example: http://sqlfiddle.com/#!2/ada12/12

Upvotes: 3

Related Questions