CODEWITHSUNDEEP
sqlstringoracle-databasesplitnewline
Sachini Karunaratne
Sachini Karunaratne

Reputation: 49

Splitting data field String value in Oracle SQl

I have a String Field value in Oracle SQL table. Is there any query so that I can split the string into new lines with certain number of equal characters in each line and the excess characters at the bottom?

eg- ABCDEFGHIJ

I want to have lines with equal number of characters 4 in each line as follows

ABCD
EFGH
IJ

The remainder of 2 letters should be at the bottom. Is it possible to achieve this using an Oracle sql query?

Upvotes: 0

Views: 135

Answers (2)

MT0
MT0

Reputation: 168361

If you have multiple rows, you can use OUTER APPLY with a hierarchical query:

SELECT s.split_value,
       s.position
FROM   table_name t
       OUTER APPLY (
         SELECT LEVEL AS position,
                SUBSTR( t.value, 4 * LEVEL - 3, 4 ) AS split_value
         FROM   DUAL
         CONNECT BY LEVEL <= CEIL( LENGTH( t.value ) / 4 )
       ) s

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'ABCDEFGHIJ' FROM DUAL UNION ALL
SELECT '123456789012' FROM DUAL;

Outputs:

SPLIT_VALUE | POSITION
:---------- | -------:
ABCD        |        1
EFGH        |        2
IJ          |        3
1234        |        1
5678        |        2
9012        |        3

db<>fiddle here

Update

if I want to have like 36 words characters in a line how can I modify your 1st answer?

SELECT s.split_value,
       s.position
FROM   table_name t
       OUTER APPLY (
         SELECT LEVEL AS position,
                 SUBSTR( t.value, 36 * ( LEVEL - 1 ) + 1, 36 ) AS split_value
         FROM   DUAL
         CONNECT BY LEVEL <= CEIL( LENGTH( t.value ) / 36 )
       ) s

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'ABCDEFGHIJ' FROM DUAL UNION ALL
SELECT '________10________20________30________40________50________60________70________80' FROM DUAL;

Outputs:

SPLIT_VALUE                          | POSITION
:----------------------------------- | -------:
ABCDEFGHIJ                           |        1
________10________20________30______ |        1
__40________50________60________70__ |        2
______80                             |        3

db<>fiddle here

Upvotes: 1

EJ Egyed
EJ Egyed

Reputation: 6094

You can use a query like the one below using CONNECT BY and LEVEL based on the length of the string.

WITH d AS (SELECT 'ABCDEFGHIJ' AS str FROM DUAL)
    SELECT SUBSTR (str, ((LEVEL - 1) * 4) + 1, 4)     AS four_letters
      FROM d
CONNECT BY LEVEL < (LENGTH (str) / 4) + 1;

Upvotes: 2

Related Questions