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 |
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 |
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)
[ \-] 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 ?.
[^--->] 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.
