SQL- Ignore case while searching for a string

Question

I have the following data in a Table
PriceOrderShipped
PriceOrderShippedInbound
PriceOrderShippedOutbound

In SQL I need to write a query which searches for a string in a table. While searching for a string it should ignore case. For the below mentioned SQL query

SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME LIKE '%PriceOrder%' 

gives all the above data, whereas

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

doesn't give.

Eg. when I search for 'PriceOrder' or 'priceOrder' it works but 'priceorder' or 'Priceorder' doesn't work. I have tried with the below query using COLLATE, but its not working. Do let me know where im going wrong.

SELECT DISTINCT COL_NAME FROM myTable WHERE 
COL_NAME COLLATE latin1_general_cs LIKE '%Priceorder%'

Answer 1

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.

Answer 2

Like this.

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

In postgresql.

Answer 3

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%')

Answer 4

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%'