Oracle DB: how can I write a query ignoring case?

Question

As I wrote in the header, I have an SQL query running on Oracle DB, let's say:

SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe' 

If I wanted the query to return either "IGNORECASE", "ignorecase", or a combination of them, how can this be done?

Is it possible?

Answer 1

You can use ALTER SESSION statements to match case-sensitive. See this FAQ .

 alter session set NLS_COMP=ANSI; alter session set NLS_SORT=BINARY_CI; 

Answer 2

 Select * from table where upper(table.name) like upper('IgNoreCaSe'); 

Alternatively, replace the lower upper.

Answer 3

You can use either the bottom or top function on both sides of the where clause

Answer 4

You can also use regular expressions:

 SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i'); 

Answer 5

You can use the upper () function in your query, and to improve performance you can use the functional base index

  CREATE INDEX upper_index_name ON table(upper(name)) 

Answer 6

You can convert both values ​​to upper or lower case using the upper or lower functions:

 Select * from table where upper(table.name) like upper('IgNoreCaSe') or lower(table.name) like lower('IgNoreCaSe'); 

Answer 7

... also converts to the top or bottom of the query:

 tableName:= UPPER(someValue || '%'); 

...

 Select * from table where upper(table.name) like tableName 

Answer 8

In addition, do not forget that you need to do data in tables? You can insert rows already in lowercase (or convert existing database rows to lowercase) and do this from the very beginning.