sql - Impossible to match a digit with a REGEXP_REPLACE -

sql - Impossible to match a digit with a REGEXP_REPLACE -

i seek extract '930' 'em 930' next regexp

regexp_replace(info,'^[:space:]*[abcdefghijklmnopqrstuvwxyz]*[:space:]*([0-9]+)[:space:]*$','\1')

but returns me original string. thought why ?

subsidiary question: why "\1" returned original string when pattern not matched ? expected homecoming null, in other regexp experiences (eg perl).

who can re-write in performant way of wel matched string of null ?

your space character class not correct. if alter [:space:] [[:space:]], regexp_replace works expect:

regexp_replace(info, '^[[:space:]]*[abcdefghijklmnopqrstuvwxyz]*[[:space:]]*([0-9]+)[[:space:]]*$','\1')

for sake of succinctness, utilize upper character class, [[:upper:]], [abcdefghijklmnopqrstuvwxyz]. changes function invocation to:

regexp_replace(info, '^[[:space:]]*[[:upper:]]*[[:space:]]*([0-9]+)[[:space:]]*$','\1')

or escape characters used in lieu of character classes:

\s space

\w word character

\d digit character

regexp_replace(info, '^\s*\w*\s*(\d+)\s*$','\1')

explanation:

since malformed character class, [:space:], not match space exists between 'em' , '930', search parameter not match characters in source parameter.

your search parameter, '^[[:space:]]*[abcdefghijklmnopqrstuvwxyz]*[[:space:]]*([0-9]+)[[:space:]]*$', anchored origin , end of column, info, can match column, info, 1 time @ most.

in case, there no match , character group, '\1' associated '([0-9]*)', has no value.

consequently, no characters replaced , left original value of column, info, 'em 930'.

interesting variations improve understand function:

-if corrected function invocation had no pattern_to_replace_by parameter, '\1', null returned:

regexp_replace(info, '^\s*\w*\s*(\d+)\s*$' ) dual;

-since have pattern_to_replace_by parameter, '\1', , has matching character group, repeating digit grouping returned:

930

sql regex oracle