Split Column value to pick last two strings

Question

In oracle, I need to find the last two values in a pipe separated column . The data looks like below

MyCol 
--------
a|b|c|d
e|f
g
h|i|j
k|l|m|n|o

My output should be as below

MyCol     ColA         ColB
----------------------------
a|b|c|d   c             d
e|f       e             f
g         null          g
h|i|j     i             j
k|l|m|n|o n             o

For two levels, I am able to write a select query using the REGEX split like below

SELECT REGEXP_SUBSTR(MyCol, '[^|]+', 1,1),REGEXP_SUBSTR(MyCol, '[^|]+', 1,2)

How can i achieve this for multiple levels.

i meant the number of pipe delimited values. a|b is 2 levels, a|b|c is 3 levels and so on — mhn, Jul 30 '15 at 12:47

Rene · Answer 1 · 2015-07-30T13:07:31.280

Managed to come up with something. Enter r=1 for the first value and r=2 for the second.

select v
  from (select v
              ,l
              ,rownum r
          from (select v
                      ,l
                  from (select regexp_substr('A|B|C|D', '[^|]+', 1, level) v
                              ,level l
                          from dual
                        connect by regexp_substr('A|B|C|D', '[^|]+', 1, level) is not null)
                 order by l desc)
         where rownum < 3)
 where r = 1

From the inside out. This query will list the individual values:

select regexp_substr('A|B|C|D', '[^|]+', 1, level) v
      ,level l
  from dual
connect by regexp_substr('A|B|C|D', '[^|]+', 1, level) is not null

A 1
B 2
C 3
D 4

The query around that reverses the order so we get:

D 4
C 3
B 2
A 1

Then we limit the result to the first two rows (rownum<3) and assign new rownumbers (rownum r)

D 1
C 2

So in the end we are able to select r=1 or r=2.

Thanks to this blog: https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Parsing regex of the format '[^|]+' fails when there are null list elements. Be careful using it. https://stackoverflow.com/questions/31464275/split-comma-seperated-values-to-columns/31464699#31464699 — Gary_W, Jul 30 '15 at 13:17
Thanks Gary, learned about regexp_count from your solution today. — Rene, Jul 31 '15 at 05:49

Gary_W · Accepted Answer · 2015-07-31T12:56:04.260

Here's a way that works with NULL elements. Its a tad brute force:

SQL> with tbl(str) as (
    select 'a|b|c|d' from dual
    union
    select 'e|f' from dual
    union
    select 'g' from dual
    union
    select 'h|i|j' from dual
    union
    select 'k|l|m|n|o' from dual
    --regexp_substr() args: string, pattern, position, occurance, match_param, subexpr
    )
      SELECT str,
             REGEXP_SUBSTR( str ,
                            '([^\|]*)(\||$)',
                            1,
                            CASE REGEXP_COUNT( str ,'\|')
                              WHEN 0 THEN NULL  -- 1 element only
                              ELSE REGEXP_COUNT( str ,'\|')
                            END,
                            NULL,
                            1 ) AS ColA,
             REGEXP_SUBSTR( str ,
                            '([^\|]*)(\||$)',
                            1,
                            CASE (REGEXP_COUNT( str ,'\|')+1)
                              WHEN 1 THEN 1-- 1 element only
                              ELSE (REGEXP_COUNT( str ,'\|')+1)
                            END,
                            NULL,
                            1 ) AS ColB
      FROM   tbl;

STR       COLA      COLB
--------- --------- ---------
a|b|c|d   c         d
e|f       e         f
g                   g
h|i|j     i         j
k|l|m|n|o n         o

SQL>

Caveat: There needs to be at least one value in the list.

Split Column value to pick last two strings

2 Answers2