Extract a number from comma separated string using regular expressions in oracle sql

Question

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

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.

Actual output from above query is:

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

Answer 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

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

Answer 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.

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.