How to split string words with regexp_substr in Oracle SQL?

Question

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

MT0 · Accepted Answer · 2022-11-24T09:22:26.970

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

score 0 · Answer 2 · answered Nov 24 '22 at 08:40

0

select  
 regexp_substr('a-b--->d--->e f','[a-z]+([ \-][a-z]+){0,1}',1,1) col1
,regexp_substr('a-b--->d--->e f','[a-z]+([ \-][a-z]+){0,1}',1,2) col2
,regexp_substr('a-b--->d--->e f','[a-z]+([ \-][a-z]+){0,1}',1,3) col3
,regexp_substr('a-b--->d--->e f','[a-z]+([ \-][a-z]+){0,1}',1,4) col4
 from dual
 ;

a-b d   e f (null)

answered Nov 24 '22 at 08:40

p3consulting

2,721
2
12
10

Can I replace a-z with others that it can a-z ,A-Z or special (&,@,#) – CaptainX Nov 24 '22 at 08:54
`[ \-]` will match space or backslash or hyphen. If you want to just match space or hyphen then you want `[ -]`. Also `{0,1}` can be simplified to `?`. – MT0 Nov 24 '22 at 09:13
right: no need to escape - when used at that position. – p3consulting Nov 24 '22 at 11:51

score 0 · Answer 3 · answered Nov 24 '22 at 08:56

[^--->] is a character set, so there is no point in specifying twice the same character in it.

A working regex is:

(-*[^->])+

That matches any character that is not an hyphen or a > with any number of possible trailing hyphens, and the pattern is repeated in a greedy way.

See a demo here.

How to split string words with regexp_substr in Oracle SQL?

3 Answers3