使用oracle sql中的正则表达式从逗号分隔的字符串中提取数字

I am trying to fetch a number which starts with 628 in a comma separated string. Below is what I am using:

我试图在逗号分隔的字符串中获取以628开头的数字。以下是我正在使用的内容：

SELECT 
    REGEXP_REPLACE(REGEXP_SUBSTR('62810,5152,,', ',?628[[:alnum:]]+,?'),',','') first,
    REGEXP_REPLACE(REGEXP_SUBSTR('5152,62810,,', ',?628[[:alnum:]]+,?'),',','') second,
    REGEXP_REPLACE(REGEXP_SUBSTR('5152,562810,,', ',?628[[:alnum:]]+,?'),',','') third,
    REGEXP_REPLACE(REGEXP_SUBSTR(',5152,,62810', ',?(628[[:alnum:]]+),?'),',','') fourth
FROM DUAL;

Its working but in one case it fails which is the third column where number is 562810. Actually I am expecting NULL in the third column.

它工作但在一种情况下它失败，这是第三列，其中数字是562810.实际上我期望在第三列中为NULL。

Actual output from above query is:

上述查询的实际输出为：

"FIRST","SECOND","THIRD","FOURTH"
"62810","62810","62810","62810"

2 个解决方案

#1

Not sure why you are using [[:alnum::]]. You could use matching group to extract the number starting with 628 or followed by a comma. REPLACE may be avoided this way

不确定为什么使用[[：alnum ::]]。您可以使用匹配组来提取以628开头的数字或后跟逗号。可以通过这种方式避免REPLACE

If you have alphabets as well, modify the 2nd match group () accordingly.

如果您也有字母，请相应地修改第二个匹配组（）。

SELECT 
    REGEXP_SUBSTR('62810,5152,,' , '(^|,)(628\d*)',1,1,NULL,2) first,
    REGEXP_SUBSTR('5152,62810,,' , '(^|,)(628\d*)',1,1,NULL,2) second,
    REGEXP_SUBSTR('5152,562810,,', '(^|,)(628\d*)',1,1,NULL,2) third,
    REGEXP_SUBSTR(',5152,,62810' , '(^|,)(628\d*)',1,1,NULL,2) fourth
FROM DUAL;

Demo

演示

#2

The problem with your regex logic is that you are searching for an optional comma before the numbers 628. This means that any number having 628 anywhere would match. Instead, you can phrase this by looking for 628 which is either preceded by either a comma, or the start of the string.

你的正则表达式逻辑的问题是你在数字628之前搜索一个可选的逗号。这意味着任何地方628的任何数字都匹配。相反，您可以通过查找628（可以是逗号或字符串的开头）来表达这一点。

SELECT
    REGEXP_REPLACE(REGEXP_SUBSTR('62810,5152,,', '(,|^)628[[:alnum:]]+,?'),',','') first,
    REGEXP_REPLACE(REGEXP_SUBSTR('5152,62810,,', '(,|^)628[[:alnum:]]+,?'),',','') second,
    REGEXP_REPLACE(REGEXP_SUBSTR('5152,562810,,', '(,|^)628[[:alnum:]]+,?'),',','') third,
    REGEXP_REPLACE(REGEXP_SUBSTR(',5152,,62810', '(,|^)(628[[:alnum:]]+),?'),',','') fourth
FROM DUAL

Demo

The ideal pattern we'd like to use here is \b628.*, or something along these lines. But Oracle's regex functions do not appear to support word boundaries, hence we can use (^|,)628.* as an alternative.

我们想在这里使用的理想模式是\ b628。*，或者沿着这些方向的东西。但是Oracle的正则表达式函数似乎不支持单词边界，因此我们可以使用（^ |，）628 *作为替代。