Regex Oracle not matching as expected

Question

I need to match and replace string like VA123 - so two letters and 3 numbers, but this expression is not working as intended. Any idea where I am going off?

SELECT REGEXP_REPLACE ('test VA123', '^\[A-Z]{2}[0-9]{3}$', 'test')
FROM dual;

I want the output in this case to say test test

You need to remove the starting anchor ^, or replace it with word boundary \b — Hao Wu
– Hao Wu, Commented Mar 16, 2021 at 0:30
thanks guys! this works SELECT REGEXP_REPLACE ('test VA123', '[A-Z]{2}[0-9]{3}$', 'test') FROM dual; — sarsnake
– sarsnake, Commented Mar 16, 2021 at 0:39
@sarsnake That would work but it would also match ABC123 which might not be what you require. — MT0
– MT0, Commented Mar 16, 2021 at 0:42

MT0 · Accepted Answer · 2021-03-16 00:40:26Z

1

It you want white space (or start of a string) before the matched string then you can use:

SELECT REGEXP_REPLACE ('test VA123', '(^|\s)[A-Z]{2}[0-9]{3}$', '\1test')
         AS replaced_value
FROM dual;

| REPLACED_VALUE |
| :------------- |
| test test      |

db<>fiddle here

answered Mar 16, 2021 at 0:40

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regex Oracle not matching as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related