Splitting String into Multiple Rows in Oracle

ORACLE: Splitting a string into multiple rows

From Oracle 12, you can use:

SELECT branch_code, year, month, day, holiday
FROM branches
CROSS JOIN LATERAL (
SELECT LEVEL AS day,
SUBSTR(holiday_list, LEVEL, 1) AS holiday
FROM DUAL
CONNECT BY LEVEL <= LENGTH(holiday_list)
)

Which, for the sample data:

CREATE TABLE branches (BRANCH_CODE, YEAR, MONTH, HOLIDAY_LIST) AS
SELECT '001', 2021, 1, 'HHHWWWHHHHWWWWWHHWWWWWHHWWWWWHH' FROM DUAL UNION ALL
SELECT '001', 2021, 2, 'WWWWWHHWWWWWHHWWWWWHHWHWWWHH' FROM DUAL

Outputs:























































































































BRANCH_CODEYEARMONTHDAYHOLIDAY
001202111H
001202112H
001202113H
001202114W
...............
0012021129W
0012021130H
0012021131H
001202121W
001202122W
001202123W
...............
0012021226W
0012021227H
0012021228H

Split String into rows Oracle SQL

This should do the trick:

SELECT DISTINCT ID, regexp_substr("Strings", '[^ ]+', 1, LEVEL)
FROM T
CONNECT BY regexp_substr("Strings", '[^ ]+', 1, LEVEL) IS NOT NULL
ORDER BY ID;

Notice how I used regexp_substr in the connect by clause too. This is to deal with the case of multiple spaces.


If you have a predictable upper bound on the number of items per line, it might worth comparing the performances of the recursive query above with a simple CROSS JOIN:

WITH N as (SELECT LEVEL POS FROM DUAL CONNECT BY LEVEL < 10)
-- ^^
-- up to 10 substrings
SELECT ID, regexp_substr("Strings", '[^ ]+', 1, POS)
FROM T CROSS JOIN N
WHERE regexp_substr("Strings", '[^ ]+', 1, POS) IS NOT NULL
ORDER BY ID;

See http://sqlfiddle.com/#!4/444e3/1 for a live demo

Splitting one row into multiple rows by breaking down the text after fixed length in oracle

The problem with your query is that while you want to split the string on blanks, you are not even trying to do that. You are using substr(value_text, 1, 30) instead, which doesn't care for where the blanks are located in the string.

You can use INSTR to find the split position. Below query works fine, as long as there is no code inside that is longer than 30 characters (in which case INSTR returns -1 which leads to a cycle in the recursive query). You may want to adjust the query for this case.

with rcte (rn, uda, value, chunk_num, value_text) as (
select rownum,
uda,
substr(trim(value_text), 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
1,
trim(substr(trim(value_text), instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
from uda_data
union all
select rn,
uda,
substr(value_text, 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
chunk_num + 1,
trim(substr(value_text, instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
from rcte
where value_text is not null
)
select uda || rn || '/' || chunk_num as uda, value
from rcte
order by rn, chunk_num;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8dfc8e55a12c4666b4bc7bfcaceea2d2

Split string into Multiple Rows and Columns using oracle

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

Oracle - split single row into multiple rows based on column value

Use a recursive sub-query factoring clause:

WITH split ( parent_item, child_item, lvl, quantity ) AS (
SELECT parent_item, child_item, 1, quantity
FROM your_table
UNION ALL
SELECT parent_item, child_item, lvl + 1, quantity
FROM split
WHERE lvl < quantity
)
SELECT parent_item, child_item, 1 As quantity
FROM split;

Or you can use a correlated hierarchical query:

SELECT t.parent_item, t.child_item, 1 AS quantity
FROM your_table t,
TABLE(
CAST(
MULTISET(
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= t.quantity
)
AS SYS.ODCINUMBERLIST
)
) l;

As for which is more performant - try benchmarking the different solutions as we cannot tell you what will be more performant on your system.

How to split a string field in several rows in Oracle Data Integrator 12c

REGEXP_SUBSTR() cannot be directly used within ODI (except can be used within a DB view) while REGEXP_REPLACE() can be for version 12. But it would be easier to operate through use of REGEXP_SUBSTR() function in order to split if it could be used. But the other function is harder to use for such splitting purposes.

Alternatively, you can use standard former functions such as SUBSTR(), INSTR() and LENGTH() along with a Hierarchical Query in order to generate rows as per split such as :

 SELECT timestamp,
CASE WHEN level = 1
THEN SUBSTR( rules, 1, INSTR(rules,',') - 1 )
WHEN level = LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
THEN SUBSTR( rules, INSTR(rules,',',1,level-1)+1,
LENGTH(rules)-INSTR(rules,',',1,level-1)+1)
ELSE SUBSTR( rules, INSTR(rules,',',1,level-1)+1,
INSTR(rules,',',1,level)-INSTR(rules,',',1,level-1)-1)
END AS rules
FROM t
CONNECT BY level <= LENGTH(rules) - LENGTH(REPLACE(rules,',')) + 1
AND PRIOR SYS_GUID() IS NOT NULL
AND PRIOR timestamp = timestamp

Demo



Related Topics



Leave a reply



Submit