CODEWITHSUNDEEP
regexoracle-database
monster
monster

Reputation: 1782

REGEXP_LIKE in Oracle

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):

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.

Upvotes: 0

Views: 277

Answers (1)

user2705585
user2705585

Reputation:

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.

Upvotes: 4

Related Questions