I have the regex \bname[^a-zA-Z] [0-9] . This regex works on https://regexr.com/ but does not work in oracle. The pattern I am going for is: <exact word "name" (upper or lower case)><any character/s that is a non-alphabet (including line breaks) or it can be no character><a whole number>

Where is my testing sample:

name8213

name:1232

name: 234

name 
1231

name: 
985

name:, 123

-- Should not match any of the below text

nameis1233

name is 123

ornaments are cool 360

nickname 1323

name 1234 1233 (should not match the second set of numbers)

However, when I execute

SELECT REGEXP_SUBSTR('name 123', '\bname[^a-zA-Z] [0-9] ', 1, 1, 'i') FROM DUAL

I get nothing out. My end goal is to just extract that whole number.

CodePudding user response：

Oracle regex does not support word boundaries.

In this case, you need to do three things:

• Use the (\W|^) as the leading word boundary construct substitute and
• Wrap the part of the regex you need to get as a return with another capturing group, and later get it using the right argument to the REGEXP_SUBSTR function
• Replace the [^a-zA-Z] with \W* (or [^[:alnum:]]* if you allow underscores there), as your intention is to match any non-word chars between name and a number, and fail all matches with letters and digits in between them.

You can use

SELECT REGEXP_SUBSTR('name 123', '(\W|^)(name\W*[0-9] )', 1, 1, 'i', 2) FROM DUAL

The last argument, 2, tells the REGEXP_SUBSTR to fetch the value of the second capturing group.

Details:

• (\W|^) - Group 1: a non-word char (any char other than a letter, digit or underscore) or start of string
• (name\W*[0-9] ) - Group 2: name, then any zero or more non-word chars (or any zero or more non-alphanumeric chars if you use [^[:alnum:]]*)and then one or more digits.

CodePudding user response：

Oracle does not support word boundaries \b in regular expressions:

SELECT value,
       REGEXP_SUBSTR(value, '(\W|^)(name[^a-zA-Z]*?\d )', 1, 1, 'n', 2) AS match
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'name8213' FROM DUAL UNION ALL
SELECT 'name:1232' FROM DUAL UNION ALL
SELECT 'name: 234' FROM DUAL UNION ALL
SELECT 'name 
1231' FROM DUAL UNION ALL
SELECT 'name: 
985' FROM DUAL UNION ALL
SELECT 'name:, 123' FROM DUAL UNION ALL
SELECT 'nameis1233' FROM DUAL UNION ALL
SELECT 'name is 123' FROM DUAL UNION ALL
SELECT 'ornaments are cool 360' FROM DUAL UNION ALL
SELECT 'nickname 1323' FROM DUAL UNION ALL
SELECT 'name 1234 1233' FROM DUAL;

Outputs:

db<>fiddle here