String parsing in oracle

Question

I have a string 425874761-G #463733361-S #48310-S. I need an SQL to make this string into a column as below.

Column_1        Column_2
425874761         G
463733361         S
48310             S

If input is 48310-S #425874761-G then output should be

Column_1        Column_2
48310              S
425874761          G

Thanks for the help in advance!

score 0 · Answer 1 · answered Jun 29 '17 at 09:21

You could use this:

WITH temp AS (
    SELECT '425874761-G #463733361-S #48310-S' col1 FROM dual
)
SELECT 
    --REGEXP_SUBSTR(t.col1, '[^#]+', 1, levels.column_value) AS split_rows,
    REGEXP_SUBSTR(REGEXP_SUBSTR(t.col1, '[^#]+', 1, levels.column_value), '[^-]+', 1, 1) AS col1,
    REGEXP_SUBSTR(REGEXP_SUBSTR(t.col1, '[^#]+', 1, levels.column_value), '[^-]+', 1, 2) AS col2
FROM 
    temp t,
    TABLE(CAST(MULTISET(SELECT level FROM dual CONNECT BY level <= LENGTH (REGEXP_REPLACE(t.col1, '[^#]+'))  + 1) AS sys.OdciNumberList)) levels;

This answer refer solution from string_to_rows and string_to_columns

String parsing in oracle

1 Answers1