Regex matching works on regex tester but not in oracle

Question

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.

The reason you don't get a match is that your string `'name 123'` does not match your pattern because it has an embedded blank after 'name'. — Bob Jarvis - Слава Україні, Nov 22 '21 at 21:48
@BobJarvis-ReinstateMonica If I put that into a regex tester website, it still matches `name 123` — Zoey Malkov, Nov 22 '21 at 21:49
`SELECT REGEXP_SUBSTR('name 123', '(\W|^)(name[^a-zA-Z]*[0-9]+)', 1, 1, 'i', 2) FROM DUAL`, but `[^a-zA-Z]+` looks out of place here. You really need `[^0-9]*`, else, `+` after `[0-9]` is redundant and you may overmatch. Ah, and it should be `*`, not `+`. — Wiktor Stribiżew, Nov 22 '21 at 21:57
@WiktorStribiżew That seems to work, but also seems to match `name 123 222`. It should not match `222` Any idea how I may stop that? — Zoey Malkov, Nov 22 '21 at 22:03
`(\W|^)(name\W*[0-9]+)`? Or `(\W|^)(name[^[:alnum:]]*[0-9]+)` if you allow underscore there. — Wiktor Stribiżew, Nov 22 '21 at 22:03

score 1 · Accepted Answer · answered Nov 22 '21 at 22:06

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.

score 0 · Answer 2 · answered Nov 22 '21 at 22:12

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:

VALUE	MATCH
name8213	name8213
name:1232	name:1232
name: 234	name: 234
name 1231	name 1231
name: 985	name: 985
name:, 123	name:, 123
nameis1233
name is 123
ornaments are cool 360
nickname 1323
name 1234 1233	name 1234

db<>fiddle here

Regex matching works on regex tester but not in oracle

2 Answers2

Linked