Split String into Rows Oracle SQL

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

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 hyphen separated string into rows in Oracle

Assuming the pattern will always be a pair of values with the same prefix ('A' here) and each followed by a number, you could use a different regular expression to extract the prefix, starting number and ending number:

SELECT
  regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 1) as prefix,
  to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 2)) as start_num,
  to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 3)) as end_num
FROM dual

PREFIX  START_NUM    END_NUM
------  ---------  ---------
A               1          4

and then use that in a recursive CTE to get the values in between:

WITH rcte (prefix, num, end_num) AS (
  SELECT
    regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 1),
    to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 2)),
    to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 3))
  FROM dual
  UNION ALL
  SELECT prefix, num + 1, end_num
  FROM rcte
  WHERE num < end_num
)
SELECT prefix || num as result
FROM rcte

RESULT
------
A1
A2
A3
A4

db<>fiddle

You could combine both approaches in one query, further assuming you don't have mixes of comma-separated values and ranges in the same string; db<>fiddle demo. If you do have a mix you could apply them in series; convert comma-separated to rows, then further process any of those new rows that are actually hyphen ranges.

Convert delimited string to rows in oracle

Riffing off this method for splitting a string while allowing for nulls:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, level, null, 1) from dual
connect by level < regexp_count('A~^B~^C~D^E','(.*?)(~\^|$)');

REGEXP_SUBS
-----------
A
B
C~D^E

As in the linked answer it looks for any characters, non-greedily, followed by the combination of ~^ (with the caret escaped, so ~\^) or end-of-line. The regexp_substr() calls also uses the optional arguments to specify subexpr - so it only gets the first grouping (.*?) and not the delimiter itself, which is in the second grouping.

If you want a specific element then that's even closer to the linked post:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, 3, null, 1) from dual;

REGEX
-----
C~D^E

Or as you're doing this in a procedure, use the connect-by query to populate a collection, and then pick out the element you need, if you'll be looking at more than one.

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

---

Related Topics

Designing a SQL Schema for a Combination of Many-To-Many Relationship (Variations of Products)

Why Can't I Use an Alias for an Aggregate in a Having Clause

How to Execute a Text File from SQL Query

Redundant Data in Update Statements

How to Create a Decimal Field in Access with Alter Table

SQL Server Search Using Like While Ignoring Blank Spaces

Insert Data into Tables Linked by Foreign Key

T-Sql: Using a Case in an Update Statement to Update Certain Columns Depending on a Condition

Display Next Event Date

How to Strip a Character Out of a Column in SQL Server

How to Select Multiple Columns from a Subquery (In SQL Server) That Should Have One Record (Select Top 1) for Each Record in the Main Query

Ora-01652: Unable to Extend Temp Segment by 128 in Tablespace System: How to Extend

SQL Query for Getting Data for Last 3 Months

How to Generate Ranks in MySQL

Concurrency Handling of SQL Transactrion

I Need to Pass Column Names Using Variable in Select Statement in Store Procedure But I Cannot Use Dynamic Query

SQL Script to Alter All Foreign Keys to Add on Delete Cascade

The Order by Clause Is Invalid in Views, Inline Functions, Derived Tables, Subqueries, and Common Table Expressions

---

Leave a reply

Submit