Search code examples
sqloracle-databasesubstr

sql query to split columns as per two attributes

I have a table say A and it has only one column say DATA.

DATA
-----
x=1;y=2;z=3
x=4;y=5;z=6
x=14;y=15;z=16;a=25

I want the result as below:

x         y          z        a 
--------------------------------
1         2          3        0
4         5          6        0
14        15         16       25

I am using toad for query processing.

Solution

  • Tested in 12c. First I split data by ';' and then by '='. 

          with dat (data) as
          (
          select 'x=1;y=2;z=3' from dual union
          select 'x=4;y=5;z=6' from dual
          )
          select 
          REGEXP_SUBSTR(REGEXP_SUBSTR(data,'[^;]+',1,1),'[^=]+',1,2) as x,
          REGEXP_SUBSTR(REGEXP_SUBSTR(data,'[^;]+',1,2),'[^=]+',1,2) as y,
          REGEXP_SUBSTR(REGEXP_SUBSTR(data,'[^;]+',1,3),'[^=]+',1,2) as z
          from dat

    Output

              X Y Z
          1 2 3
          4 5 6