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_CODE YEAR MONTH DAY HOLIDAY 001 2021 1 1 H 001 2021 1 2 H 001 2021 1 3 H 001 2021 1 4 W ... ... ... ... ... 001 2021 1 29 W 001 2021 1 30 H 001 2021 1 31 H 001 2021 2 1 W 001 2021 2 2 W 001 2021 2 3 W ... ... ... ... ... 001 2021 2 26 W 001 2021 2 27 H 001 2021 2 28 H
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 caseINSTR
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 filesWhich, 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.idOr, 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) whileREGEXP_REPLACE()
can be for version 12. But it would be easier to operate through use ofREGEXP_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()
andLENGTH()
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 = timestampDemo
Related Topics
How to in a Query Calculate the Sum of the Union
Localhost/Phpmyadmin Giving Page Not Found Error
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Sql Get Parent Where Children Have Specific Values
Oracle Sql, Concatenate Multiple Columns + Add Text
Postgresql Delete Multiple Rows from Multiple Tables
How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server
How to Execute a Stored Procedure Once for Each Row Returned by Query
How to Calculate Age (In Years) Based on Date of Birth and Getdate()
Extract Number from String With Oracle Function
How to Get Column Name Based on Row Value in SQL Server
Query to Get All Those Names of Employees,Who Have 'A' as Their Middle Character in Their Name
How to Close Idle Connections in Postgresql Automatically
Removing Leading Zeroes from a Field in a SQL Statement
How to Select True/False Based on Column Value
Finding the Highest Average Salary in SQL
Insert Value into Table If Conidition Is Met At Least One Time
Select Every Employee That Has a Higher Salary Than the Average of His Department