根据连字符拆分字符串

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

我想根据连字符将字符串分成两部分，即XX0099和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.

我们有一个旧的遗留系统仍然使用8i Oracle数据库。

2 个解决方案

#1

Using combination of substr and instr:

使用substr和instr的组合：

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

Another way is using regexp_substr if supported:

另一种方法是使用regexp_substr（如果支持）：

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

#2

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

如果你不能使用正则表达式子字符串/替换，这里有一个使用Oracle的基本字符串函数的选项：

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

#1