CODEWITHSUNDEEP
regexoracle-databaseoracle11g
adbdkb
adbdkb

Reputation: 2161

How to formulate Regular Expression correctly in Oracle that works in other engines

Need help if formulating a regular expression that will work in Oracle. And if someone knows of a regular expression tester specifically for oracle, that will also help.

I am trying the following regex 

^([A-Za-z]{2,4})([\s0]*)([1-9][0-9]{0,5})([\s]*)([A-Za-z]?)$

on APLS 0000 1024 C, I get the correct results with this tester, but when I try the below in Oracle, it doesn't return 'YES' as expected .

select 'Yes' from dual where REGEXP_Like
     ('APLS   0000    1024  C',  '^([A-Za-z]{2,4})([\s]*)([0]*)([1-9][0-9]{0,5})([\s]*)([A-Za-z]?)$');

So, I am trying to understand how to make it work in Oracle, where I will be able to get the 3 tokens - APLS, 1024 and C' when I replace the REGEXP_LIKE with REGEXP_REPLACE, of course with appropriate changes in the SQL statement.

Thanks

Upvotes: 1

Views: 62

Answers (1)

collapsar
collapsar

Reputation: 17238

Your have 2 issues with your regex:

  • regex wouldn't match your test string.
  • \s cannot be used in character classes.

You have ...

^([A-Za-z]{2,4})([\s]*)([0]*)([1-9][0-9]{0,5})([\s]*)([A-Za-z]?)$
                   ^         ^                   ^
                   |         |                   +-- problem #2 (2nd instance)
                   |         +--- problem #1: you need to match a whitespace sequence here
                   +--- problem #2: escape sequence used in character class

Instead take ...

^([A-Za-z]{2,4})(\s*)([0]*)(\s*)([1-9][0-9]{0,5})(\s*)([A-Za-z]?)$

Upvotes: 1

Related Questions