我使用了一个案例声明，但仍然无法正常工作。我该如何解决。
WITH tst
AS
(
SELECT '639 - xadfa dfdsa euwere (15-30Min)' str FROM DUAL
UNION
SELECT 'AB/NCDSDFsd - 218' FROM DUAL
UNION
SELECT '141 - Uxsdfasd Zebasdased ABC3' FROM DUAL
)
SELECT
str,
CASE
WHEN LENGTH(TRIM(SUBSTR(TRIM(REGEXP_REPLACE(str, '([^[:digit:] ])', '')),-3,3))) = 3
THEN TRIM(SUBSTR(TRIM(REGEXP_REPLACE(str, '([^[:digit:] ])', '')),-3,3))
ELSE
CASE
WHEN LENGTH(TRIM(SUBSTR(TRIM(REGEXP_REPLACE(str, '([^[:digit:] ])', '')),1,3))) = 3
THEN TRIM(SUBSTR(TRIM(REGEXP_REPLACE(str, '([^[:digit:] ])', '')),1,3))
ELSE NULL
END
END num_val
FROM tst;
查询结果：
STR NUM_VAL
--------------------------------------------
141 - Uxsdfasd Zebasdased ABC3 141
639 - xadfa dfdsa euwere (15-30Min) 530
AB/NCDSDFsd - 218 218
如果字符串以一个连续的3位数字开头，那么我需要该第一个3位数字。如果字符串以字母开头，那么我需要在字符串末尾设置3位数字。预期产量：
STR NUM_VAL
--------------------------------------------
141 - Uxsdfasd Zebasdased ABC3 141
639 - xadfa dfdsa euwere (15-30Min) 639
AB/NCDSDFsd - 218 218
重新制定的问题可以解决，例如，如下所示：
WITH tst
AS
(
SELECT '639 - xadfa dfdsa euwere (15-30Min)' str FROM DUAL UNION ALL
SELECT 'AB/NCDSDFsd - 218' FROM DUAL UNION ALL
SELECT 'dsafas 123 COMP - 751' FROM DUAL UNION ALL
SELECT '141 - Uxsdfasd Zebasdased ABC3' FROM DUAL
)
select str, regexp_substr(str, '(^\d{3}|\d{3}$)') as num
from tst;
STR NUM
----------------------------------- -------------------
639 - xadfa dfdsa euwere (15-30Min) 639
AB/NCDSDFsd - 218 218
dsafas 123 COMP - 751 751
141 - Uxsdfasd Zebasdased ABC3 141
正则表达式是一个交替。^并且$是锚点-它们要求片段分别位于输入字符串的开头和结尾。( ... | ... )表示找到第一个替代方案，如果找不到，则找到第二个替代方案。\d{3}表示正好3位数。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句