SQL- Ignore case while searching for a string

Solution 1:

Use something like this -

SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')

or

SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')

Solution 2:

Like this.

SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME iLIKE '%Priceorder%'

In postgresql.

Solution 3:

See this similar question and answer to searching with case insensitivity - SQL server ignore case in a where expression

Try using something like:

SELECT DISTINCT COL_NAME 
FROM myTable 
WHERE COL_NAME COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%priceorder%'

Solution 4:

You should probably use SQL_Latin1_General_Cp1_CI_AS_KI_WI as your collation. The one you specify in your question is explictly case sensitive.

You can see a list of collations here.

Solution 5:

If someone using ORACLE can be changes at database level itself

ALTER SESSION SET NLS_COMP=LINGUISTIC;  
ALTER SESSION SET NLS_SORT=BINARY_CI;  

Note:- Oracle