1

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!

Ben Aflek
  • 33
  • 4
  • Do you have access to any other tools besides Oracle? This is a data scrubbing problem, and a a database is not really the best place to be doing this. – Tim Biegeleisen Sep 03 '19 at 13:29
  • Well, I should write a script that fills a table with values based on this column, I should do everything in oracle sql. – Ben Aflek Sep 03 '19 at 13:31
  • If you really want to do this in Oracle, you might need to write a stored procedure. – Tim Biegeleisen Sep 03 '19 at 13:32
  • 1
    Do the values only have digits? And also, is always the type in `*`, `#` ? The second question is not that clear to me, can you please post some sample data and needed result? – Aleksej Sep 03 '19 at 13:34
  • Check out this related question for some good answers about this general class of problem. https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle – kfinity Sep 03 '19 at 13:35
  • It definitely should be a procedure, my first step should be this parsing before I can do anything. – Ben Aflek Sep 03 '19 at 13:35
  • @Aleksej yes, inbetween are only numbers. Sample data for bonus question column1(id)|column2(value) 1 | #123##254#*78* If value that need to be parsed has id of 1, than parse column2. Thanks – Ben Aflek Sep 03 '19 at 13:36
  • @kfinity thanks, I will check it out. – Ben Aflek Sep 03 '19 at 13:38
  • @Aleksej and also yes, types are only # and * – Ben Aflek Sep 03 '19 at 13:39
  • @kfinity I checked that out, the problem that i face is that values are delimited from both sides. After I parse the values, I want to be left with only numbers – Ben Aflek Sep 03 '19 at 13:41
  • What is the purpose of the id column if it is always 1? – bk_32 Sep 03 '19 at 14:26
  • please show your effort, and what you have tried so far .. – Barbaros Özhan Sep 03 '19 at 19:58

1 Answers1

1

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.

kfinity
  • 8,581
  • 1
  • 13
  • 20