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