sqloracle-databaseregexp-substr

How to split string words with regexp_substr in Oracle SQL?


select  
regexp_substr('a-b--->d--->e f','[^--->]+',1,1) col1
,regexp_substr('a-b--->d--->e f','[^--->]+',1,2) col2
,regexp_substr('a-b--->d--->e f','[^--->]+',1,3) col3
,regexp_substr('a-b--->d--->e f','[^--->]+',1,4) col4
 from dual

output

col1 col2 col3 col4
a b d e f

Required output

col1 col2 col3 col4
a-b d e f

Solution

  • You can use:

    select regexp_substr(value,'(.*?)(-+>|$)',1,1, NULL, 1) AS col1
    ,      regexp_substr(value,'(.*?)(-+>|$)',1,2, NULL, 1) AS col2
    ,      regexp_substr(value,'(.*?)(-+>|$)',1,3, NULL, 1) AS col3
    ,      regexp_substr(value,'(.*?)(-+>|$)',1,4, NULL, 1) AS col4
     from  table_name
    

    or, if you are looking for the fixed delimiter string ---> then you can use simple string functions (which are more to type but are likely going to be much faster):

    SELECT CASE
           WHEN pos1 = 0 THEN value
                         ELSE SUBSTR(value, 1, pos1 - 1)
           END AS col1,
           CASE
           WHEN pos1 = 0 THEN NULL
           WHEN pos2 = 0 THEN SUBSTR(value, pos1 + 4)
                         ELSE SUBSTR(value, pos1 + 4, pos2 - pos1 - 4)
           END AS col2,
           CASE
           WHEN pos2 = 0 THEN NULL
           WHEN pos3 = 0 THEN SUBSTR(value, pos2 + 4)
                         ELSE SUBSTR(value, pos3 + 4, pos3 - pos2 - 4)
           END AS col3,
           CASE
           WHEN pos3 = 0 THEN NULL
                         ELSE SUBSTR(value, pos3 + 4)
           END AS col4
    FROM   (
      SELECT value,
             INSTR(value, '--->', 1, 1) AS pos1,
             INSTR(value, '--->', 1, 2) AS pos2,
             INSTR(value, '--->', 1, 3) AS pos3
      FROM   table_name
    )
    

    Which, for the sample data:

    CREATE TABLE table_name (value) AS
    SELECT 'a-b--->d--->e f' FROM DUAL;
    

    Both output:

    COL1 COL2 COL3 COL4
    a-b d e f null

    fiddle