CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11gsplit
Ben Aflek
Ben Aflek

Reputation: 33

Parse value delimited from both sides of it into multiple rows

I have a column value that is like this

#123##456##789#*0123*, (each value is parsed on both sides) and I want to parse it to look like this:

id value type
1 123     #
1 456     #
1 789     #
1 0123    *

How should I do it?

Bonus question, I want to pass id in the query, and the parser should know the column that it should parse (I don't want to parse static value), how would that query look like. Thanks in advance!

Upvotes: 1

Views: 66

Answers (1)

kfinity
kfinity

Reputation: 9091

Ok, I modified MT0's recursive CTE answer from the link above to handle two delimiters (ie, on both ends), and to pull out the delimiter into a separate column. Let me know if you have any questions.

with example as (select 1 as id, '#123##456##789#*0123*' as str from dual
                union select 2, '#837#*827*#3021#*013*' from dual),
  t ( id, str, start_pos, end_pos ) AS
    ( SELECT id, str, 1, REGEXP_INSTR( str, '[^0-9]' ) FROM example
    UNION ALL
    SELECT id,
      str,
      end_pos                    + 1,
      REGEXP_INSTR( str, '[^0-9]', end_pos + 1 )
    FROM t
    WHERE end_pos > 0
    )
SELECT id, 
  --str, start_pos, end_pos, -- uncomment for debugging
  SUBSTR( str, start_pos, DECODE( end_pos, 0, LENGTH( str ) + 1, end_pos ) - start_pos ) AS value,
  substr(str, start_pos-1, 1) as type
FROM t
where start_pos <> end_pos and end_pos <> 0
  -- bonus question - uncomment to filter by ID
  --and id = 1
ORDER BY id,
  start_pos;

FYI - this will drop null values (eg "##") and not display them as a row.

Upvotes: 1

Related Questions