SQL regexp_substr获取连字符前后的字符串

Question

我将数字及其类型存储在oracle DB中的单个列中。使用sql，我想拆分它们并返回给用户。

例如 ABC-1234 ABC是类型 1234是数字

到目前为止，我试过这个。是否存在 ABC 或 BN 或 XY 或 GF 我想返回空

值

SELECT 'ABC-1234' TET_NUMBER FROM DUAL
UNION ALL
SELECT 'XY - 87686876' TET_NUMBER FROM DUAL
UNION ALL  
SELECT ' BN-345345' TET_NUMBER FROM DUAL
UNION ALL   
SELECT ' GF - 16666788789789' TET_NUMBER FROM DUAL
UNION ALL   
SELECT '987654' TET_NUMBER FROM DUAL
SELECT regexp_substr(TET_NUMBER, '[^-]+', 1, 1) AS PARTBEFORE- 
SELECT regexp_substr(TET_NUMBER, '[^-]+', 1, 1) AS PARTAFTER-

预期产出

ABC 1234
XY  87686876
BN  345345
GF  16666788789789

Answer 1

在这里：第一部分选择一个"单词"(\w(，而第二部分选择一堆"数字"(\d(。WHERE子句避免使用非字母行。

SQL> with test as
2  (SELECT 'ABC-1234' TET_NUMBER FROM DUAL
3   UNION ALL
4   SELECT 'XY - 87686876' TET_NUMBER FROM DUAL
5   UNION ALL
6   SELECT ' BN-345345' TET_NUMBER FROM DUAL
7   UNION ALL
8   SELECT ' GF - 16666788789789' TET_NUMBER FROM DUAL
9   UNION ALL
10   SELECT '987654' TET_NUMBER FROM DUAL
11  )
12  select tet_number,
13    regexp_substr(tet_number, 'w+') first_part,
14    regexp_substr(tet_number, 'd+') second_part
15  from test
16  where regexp_like(tet_number, '[[:alpha:]]+')
17  ;
TET_NUMBER           FIRST_PART      SECOND_PART
-------------------- --------------- ---------------
ABC-1234             ABC             1234
XY - 87686876        XY              87686876
BN-345345           BN              345345
GF - 16666788789789 GF              16666788789789
SQL>

[编辑：如果输入字符串发生变化怎么办，如OP的评论中所述]

没什么;你会调整正则表达式，比如

<snip>
14  select
15    tet_number,
16    regexp_substr(tet_number, 'w+', 1, 1) first_part,
17    regexp_substr(tet_number, 'w+', 1, 2) second_part
18  from test
19  where regexp_like(tet_number, '[[:alpha:]]+');
TET_NUMBER           FIRST_PART      SECOND_PART
-------------------- --------------- ---------------
ABC-1234             ABC             1234
XY - 87686876        XY              87686876
BN-345345           BN              345345
GF - 16666788789789 GF              16666788789789
ABC-1W234            ABC             1W234