Oracle DB How can I write query ignoring case

Question

As I had written in title  I have SQL query  run on Oracle DB  lets say   SELECT   FROM TABLE WHERE TABLE NAME Like  IgNoReCaSe    If I would like  that the query would return either  IGNORECASE    ignorecase  or combinations of them  how can this be done   Is this possible

User · Answer

also do the conversion to upper or lower outside of the query   tableName   UPPER someValue                 Select   from table where upper table name  like tableName

User · Answer

Use ALTER SESSION statements to set comparison to case-insensitive  alter session set NLS COMP LINGUISTIC  alter session set NLS SORT BINARY CI   If you re still using version 10gR2  use the below statements  See this FAQ for details  alter session set NLS COMP ANSI  alter session set NLS SORT BINARY CI

User · Answer

Also don t forget the obvious  does the data in the tables need to have case   You could only insert rows already in lower case  or convert the existing DB rows to lower case  and be done with it right from the start

User · Answer

You can use the upper   function in your query  and to increase performance you can use a function-base index   CREATE INDEX upper index name ON table upper name

User · Answer

Select   from table where upper table name  like upper  IgNoreCaSe      Alternatively  substitute lower for upper

User · Answer

In version 12 2 and above  the simplest way to make the query case insensitive is this  SELECT   FROM TABLE WHERE TABLE NAME COLLATE BINARY CI Like  IgNoReCaSe

User · Answer

You could also use Regular Expressions   SELECT   FROM TABLE WHERE REGEXP LIKE  TABLE NAME  IgNoReCaSe   i

User · Answer

You can convert both values to upper or lowercase using the upper or lower functions   Select   from table where upper table name  like upper  IgNoreCaSe     or  Select   from table where lower table name  like lower  IgNoreCaSe

User · Answer

You can use either lower or upper function on both sides of the where condition

[sql] Oracle DB: How can I write query ignoring case?

Examples related to sql

Examples related to database

Examples related to oracle