Ignore Case In Query

Question

Possible Duplicate:
Oracle DB: How can I write query ignoring case?

I have an sql query it and the where condition contains english and arabic characters so i can not use toupper or tolower because the arabic characters will be shown as question marks.

DBMS is oracle

Answer 1

You mean lower() and upper() right?

Oracle supports many different languages, which means you need to use a nls function. In this case nls_lower.

select nls_lower('my string', 'NLS_SORT = ARABIC') from dual

This may cause problems with latin characters - it's worth checking your output, so you might want to do something similar to the below to mitigate:

select case when instr(lower('my_string'),'?') > 0
                 then nls_lower('my string', 'NLS_SORT = ARABIC')
            else lower('my_string')
  from dual