CODEWITHSUNDEEP
oracle-database
Ram
Ram

Reputation: 731

How to select second split of column data from oracle database

I want to select the data from a Oracle table, whereas the table columns contains the data as , [ex : key,value] separated values; so here I want to select the second split i.e, value

table column data as below :

column_data
++++++++++++++
asper,worse
tincher,good
golder
null                       -- null values need to eliminate while selection
www,ewe

from the above data, desired output like below:

column_data
+++++++++++++
worse
good
golder
ewe

Please help me with the query

Upvotes: 0

Views: 41

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

According to data you provided, here are two options:

  • result1: regular expressions one (get the 2nd word if it exists; otherwise, get the 1st one)
  • result2: SUBSTR + INSTR combination
SQL> with test (col) as
  2    (select 'asper,worse'  from dual union all
  3     select 'tincher,good' from dual union all
  4     select 'golder'       from dual union all
  5     select null           from dual union all
  6     select 'www,ewe'      from dual
  7    )
  8  select col,
  9         nvl(regexp_substr(col, '\w+', 1, 2), regexp_substr(col, '\w+', 1,1 )) result1,
 10         --
 11         nvl(substr(col, instr(col, ',') + 1), col) result2
 12  from test
 13  where col is not null;

COL          RESULT1              RESULT2
------------ -------------------- --------------------
asper,worse  worse                worse
tincher,good good                 good
golder       golder               golder
www,ewe      ewe                  ewe

SQL>

Upvotes: 1

Related Questions