REGEXP_LIKE in Oracle

Question

I have a query which I was using in an Access database to match a field. The rows I wish to retrieve have a field which contains a sequence of characters in two possible forms (case-insensitive):

*PO12345, 5 digits preceded by *PO, or
PO12345, 5 digits preceded by PO.

In Access I achieved this with:

WHERE MyField LIKE '*PO#####*'

I have tried to replicate the query for use in an Oracle database:

WHERE REGEXP_LIKE(MyField, '/\*+PO[\d]{5}/i')

However, it doesn't return anything. I have tinkered with the Regex slightly, such as placing brackets around PO, but to no avail. To my knowledge what I have written is correct.

I think it should be WHERE REGEXP_LIKE(MyField, '\*?PO\d{6}') — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 9, 2016 at 16:52
I've just edited the question - I should've said 5 digits in the question. Pardon my haste. — monster
– monster, Commented Mar 9, 2016 at 16:52
If there are more than 5 digits, should the string still match the regex? — David Faber
– David Faber, Commented Mar 10, 2016 at 15:41

score 4 · Accepted Answer · 2016-03-10 05:16:39Z

4

Your regex \*+PO[\d]{5} is wrong. There shouldn't be + after \* as it's optional.

Using ? like this /\*?PO\d{5}/i solves the problem.

Use i (case insensitive) as parameter like this: REGEXP_LIKE (MyField, '^\*?PO\d{5}$', 'i');

Regex101 Demo

Read REGEXP_LIKE documentation.

edited Mar 10, 2016 at 5:16

answered Mar 9, 2016 at 16:53

user2705585

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

6 Comments

monster Over a year ago

Yes, sorry I don't know why I was using + instead of ?; however, it still returns nothing.

David Faber Over a year ago

I would add start and end anchors too, so REGEXP_LIKE(myfield, '^\*?PO\d{5}$', 'i') - otherwise the regex will match strings like ABCD*PO1234567890, etc.

monster Over a year ago

@DavidFaber *PO12345 is within the field somewhere; it does not make up the whole field.

monster Over a year ago

@noob thank you - it works now. I initially missed one of your more subtle changes: [\d] to \d.

David Faber Over a year ago

@monster, thanks for the correction - I did not read the entire question carefully. If that be the case then the * can be omitted entirely in the regex since PO\d{5} matches *PO12345

|

Collectives™ on Stack Overflow

REGEXP_LIKE in Oracle

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related