CODEWITHSUNDEEP
sqloracle-database
Manoj Naik
Manoj Naik

Reputation: 396

Split string into Multiple Rows and Columns using oracle

I working on one SP which will split data present in one of the input parameter to multiple rows and column and will insert the same into table.

Format of the that string will be <ID>-<Name>|<ID1>-<Name1>|<ID2>-<Name2> e.g. 145-Test.txt|236-Test1.png

Expected output of the the provided example will be as below

ID  |  Name
145    Test.txt
236    Test1.png

Using below query I am able to split data into rows on the basis of | separator

select   REGEXP_SUBSTR('145-Test.txt|236-Test1.png','[^|]+', 1, level) from DUAL
connect by regexp_substr('145-Test.txt|236-Test1.png', '[^|]+', 1, level) is not null;

also using below query I am able to split string data into columns on the basis of - separator

select REGEXP_SUBSTR('145-Test.txt', '[^-]+', 1, 1) ID,
       REGEXP_SUBSTR('145-Test.txt', '[^-]+', 1, 2) Name
FROM DUAL;

Need help to merge these two queries so that I can get the expected output from single query only.

Upvotes: 0

Views: 587

Answers (1)

MT0
MT0

Reputation: 168613

You can use simple string functions:

WITH files ( value, start_pos, hyphen_pos, end_pos ) AS (
  SELECT value,
         1,
         INSTR( value, '-', 1, 1 ),
         INSTR( value, '|', 1, 1 )
  FROM   table_name
UNION ALL
  SELECT value,
         end_pos + 1,
         INSTR( value, '-', end_pos + 1, 1 ),
         INSTR( value, '|', end_pos + 1, 1 )
  FROM   files
  WHERE  end_pos > 0
)
SELECT TO_NUMBER( SUBSTR( value, start_pos, hyphen_pos - start_pos ) ) AS id,
       CASE end_pos
       WHEN 0
       THEN SUBSTR( value, hyphen_pos + 1 )
       ELSE SUBSTR( value, hyphen_pos + 1, end_pos - hyphen_pos - 1 )
       END AS filename
FROM   files

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT '145-Test.txt|236-Test1.png' FROM DUAL UNION ALL
SELECT '999-Test2_1.id|998-Test2_2.id' FROM DUAL UNION ALL
SELECT '555-Test3_1.id' FROM DUAL UNION ALL
SELECT NULL FROM DUAL;

Outputs:

  ID | FILENAME  
---: | :---------
 145 | Test.txt  
 999 | Test2_1.id
 555 | Test3_1.id
null | null      
 236 | Test1.png 
 998 | Test2_2.id

Or, if you want to use regular expressions:

SELECT id,
       filename
FROM   table_name
       CROSS APPLY (
         SELECT TO_NUMBER( REGEXP_SUBSTR( value, '(\d+)-(.*?)(\||$)', 1, LEVEL, NULL, 1 ) )
                  AS id,
                REGEXP_SUBSTR( value, '(\d+)-(.*?)(\||$)', 1, LEVEL, NULL, 2 )
                  AS filename
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT( value, '(\d+)-(.*?)(\||$)' )
       );

or you can adapt your query:

SELECT id,
       filename
FROM   table_name
       CROSS APPLY (
         SELECT REGEXP_SUBSTR( value,'[^|-]+', 1, 2 * level - 1 ) AS id,
                REGEXP_SUBSTR( value,'[^|-]+', 1, 2 * level - 0 ) AS filename
         FROM   DUAL
         CONNECT BY 2 * LEVEL <= REGEXP_COUNT( value, '[^|-]+' )
       );

Which both output the same as the simple string functions.

db<>fiddle here

Upvotes: 2

Related Questions