padibro padibro - 7 months ago 9
SQL Question

How to use regular expression in Oracle SQL query

I have a table with a column 'DESCRIPTION'.
I would like retrieve, by a regular expression, only rows with at least one lower case character.

I have tried

select * from MYTABLE t
WHERE REGEXP_LIKE (t.DESCRIPTION, '[a-z]');


but the result is equal to

select * from MYTABLE t

Answer

You may need to explicily force a case sensitive comparison:

select *
from  MYTABLE t 
WHERE REGEXP_LIKE (t.DESCRIPTION, '[a-z]', 'c')

From Oracle documentation:

If you omit match_parameter, then:

The default case sensitivity is determined by the value of the NLS_SORT parameter