Parsing pipe delimited, irregular strings in Oracle

Question

I have data and a similar problem to this question: Parsing pipe delimited string into columns?

However my data can contain sub strings with no data, or strings that are incomplete. Note that 5 values is the maximum. The solution in the question above fails unless the string has 5 values. See below:

KEY         VALUE                               
----        -------------------
00          val1||||val5                                
01          val2|val2|val3|                        
02          val1|val2||val4

What is the output that you want? One row with five columns? Between 0 ad 5 rows with two columns? Something else? I'm not sure what you're trying with, presumably, `regexp_substr` and what "fails". — Justin Cave, Dec 15 '15 at 16:17
I need the value column split on the delimiters into 5 columns. `select regexp_substr('val1|val2|||val5', '[^|]+', 1, 3) from dual` results in `val5` as it's the 3rd value according to the criteria. My goal is to move this data into a purposed table. — Half_Duplex, Dec 15 '15 at 16:25
It would be helpful if you edited that into your question (both the code you're using and the output you want given that input data). — Justin Cave, Dec 15 '15 at 16:29

score 1 · Answer 1 · answered Dec 15 '15 at 16:29

If using PL/SQL and a loop is OK you could use apex_util.string_to_table:

declare
   l_tab apex_application_global.vc_arr2;
begin
   for r in (select key, value from mytable) loop
      l_tab := apex_util.string_to_table (r.value, '|');
      dbms_output.put_line ('key='||r.key);
      for i in 1..l_tab.count loop
         dbms_output.put_line ('value ' || i || '='||l_tab(i));
      end loop;
   end loop;
end;

score 1 · Accepted Answer · answered Dec 15 '15 at 16:42

Setup:

CREATE TABLE TABLE_NAME ( KEY, VALUE ) AS
SELECT '00',          'val1||||val5' FROM DUAL UNION ALL
SELECT '01',          'val2|val2|val3|' FROM DUAL UNION ALL
SELECT '02',          'val1|val2||val4' FROM DUAL;

Query 1:

SELECT  Key,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 1, NULL, 1 ) AS val1,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 2, NULL, 1 ) AS val2,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 3, NULL, 1 ) AS val3,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 4, NULL, 1 ) AS val4,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 5, NULL, 1 ) AS val5
FROM    table_name

Results:

KEY VAL1            VAL2            VAL3            VAL4            VAL5          
--- --------------- --------------- --------------- --------------- ---------------
00  val1                                                            val5            
01  val2            val2            val3                                            
02  val1            val2                            val4

Thanks, this appears to work. Could you please help me understand your usage of the match and subexpression params and the second capture group? Why doesn't a single `end with |` work the same way? — Half_Duplex, Dec 15 '15 at 17:39
To match an empty string between `||` you need to change from `[^|]+` to a zero-width match `[^|]*`. However, this has the side effect of creating two matches when you have `|val|` - a match for `val` and then an unwanted zero-width match before the delimiter; to get round this you need to ensure you include the delimiter in the match `[^|]*\|` but that won't match the last value so you need to either match a delimiter or the end-of-string `[^|]*(\||$)`. Which gives you what you want but includes the delimiter so you can use a capturing group just to extract the required value `([^|]*)(\||$)`. — MT0, Dec 15 '15 at 19:26

Parsing pipe delimited, irregular strings in Oracle

2 Answers2

Related