Split a string based on hyphen

Question

I have a string like this

XX0099-X01

I would like to split the string into two based on the hyphen, i.e. XX0099 and X01

I have tried as

SELECT 'XX0099-X01',
           SUBSTR('XX0099-X01',
                  1,
                  INSTR('XX0099-X01',
                        '-',
                        1
                        ) -1
                  )
    FROM   dual

Not sure how to get the second part.

We have an old legacy system which still using 8i Oracle database.

Gurwinder Singh · Accepted Answer · 2017-02-05T04:15:51.293

4

Using combination of substr and instr:

select 
  substr(s, 1, instr(s, '-') - 1),
  substr(s, instr(s, '-') + 1)
from t;

Another way is using regexp_substr if supported:

select 
  regexp_substr('XX0099-X01','[^-]+', 1, 1),
  regexp_substr('XX0099-X01','[^-]+', 1, 2)
from dual;

edited Feb 05 '17 at 04:15

answered Feb 04 '17 at 07:21

Gurwinder Singh

38,557
6
51
76

We have an old legacy system which still using 8i Oracle database, the above is not working in 8i. Thanks – Jacob Feb 04 '17 at 07:23
Thanks a lot, much appreciated. – Jacob Feb 04 '17 at 07:28
The "older version" (using standard string functions) is also likely to be faster than regular expression solutions in most cases. In general, regular expressions should be used only if a solution with standard string functions does not exist (or if the only such solutions are too complicated and cumbersome, becoming difficult to manage/maintain). – Feb 04 '17 at 19:38

Tim Biegeleisen · Answer 2 · 2017-02-04T07:30:17.083

4

If you can't use a regex substring/replacement, here is an option which uses Oracle's base string functions:

SELECT SUBSTR(col, 1, INSTR(col, '-') - 1) AS first_part,
       SUBSTR(col, INSTR(col, '-') + 1)    AS second_part
FROM yourTable

edited Feb 04 '17 at 07:30

answered Feb 04 '17 at 07:23

Tim Biegeleisen

502,043
27
286
360

Thanks a lot, much appreciated. – Jacob Feb 04 '17 at 07:28

Split a string based on hyphen

2 Answers2