What's the best way to extract 2nd number from a string

Dito 0
Viewed 28

I have something like below stored in a table column. I need only 133 extracted from this.

015.133.Governmental Affairs

When I do

select regexp_substr('015.133.Governmental Affairs', '\.*+[[:digit:]]+*',1,2) from dual;

The result is .133

If I do

regexp_substr('015.133.Governmental Affairs', '\*+[[:digit:]]+*',1,2)

it returns nothing. What's correct expression here?

regexp-substr
2 Answers

The trick with coming up with a good regex is to be able to explain it in plain language first.

Editing to explain better hopefully.

Here I am matching zero or more digits where followed by a literal period. The 4th argument to REGEXP_SUBSTR (2) is which occurrence of this pattern to match on. Note the pattern consists of 2 groups as defined by being surrounded by parentheses. The 6th argument to REGEXP_SUBSTR says when a match is found to return the 1st subgroup (the numbers, not the period), if you put a 2 there you'd get the period that follows the number 133.

SELECT REGEXP_SUBSTR('015.133.Governmental Affairs', '([[:digit:]]*?)(\.)', 1, 2, NULL, 1) AS nbr
FROM dual;


NBR
---
133
1 row selected.
Gary_W0

Here's something adapted from this question: How to extract group from regular expression in Oracle?

SELECT REGEXP_REPLACE(
    '015.133.Governmental Affairs',
    '^[[:digit:]]+\.([[:digit:]]+)\..*',
    '\1'
) FROM DUAL;

The regex looks for a string that starts with a series of digits, then ., then more digits, then another ., then the rest of the string. It then replaces the entire match (which is the entire string) with \1, which is whatever was in that second set of digits, inside the parentheses.

Andy Lester0
Related