CODEWITHSUNDEEP
javasqlregexoracle-database
ImranRazaKhan
ImranRazaKhan

Reputation: 2297

Regular Expression in Oracle Sql

My following SQL is not working but when I use same regular expression in Java, it's work fine

SELECT * FROM tbl WHERE REGEXP_LIKE
(MESSAGE, '^(92|0)?(3[0-9]{9})\\*([0-9]{4})\\*(([0-9]{3})|1000)$')

Please help what I am doing wrong.

i am comparing following string

03211234090*0000*75

Upvotes: 1

Views: 247

Answers (2)

Rachcha
Rachcha

Reputation: 8816

There is a bug in your search pattern: \\*. This should be \*, as you only need one \ to escape the *.

SELECT *
FROM tbl
WHERE REGEXP_LIKE(MESSAGE, '^(92|0)?(3[0-9]{9})\*([0-9]{4})\*(([0-9]{3})|1000)$')

The other problem is in the \*(([0-9]{3})|1000)$ part - the last part of your string. The string you have provided 03211234090*0000*75 does not end with three digits between 0-9 or "1000".

$ means the string must end with the expression immediately preceding it.

As a solution try putting \*(([0-9]{2, 3})|1000)$ if your business logic allows this. This will allow the either two or three digits or 1000 after the last asterisk to be accepted and the row will be retrieved.

Upvotes: 3

A.B.Cade
A.B.Cade

Reputation: 16905

The real question should be: how come it's working in java?
Since the string 03211234090*0000*75 doesn't match your regular expression - the las part (after the second asterix needs to have 3 digits or 1000 and you have only 2)

Upvotes: 3

Related Questions