SQL-Ignore case when searching for a string

Question

I have the following data in a table
PriceOrderShipped
PriceOrderShippedInbound
PriceOrderShippedOutbound

In SQL, I need to write a query that looks for a row in a table. When searching for a string, it must ignore case. For the SQL query below

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

gives all of the above data, whereas

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

does not give.

Eg. when I search for "PriceOrder" or "priceOrder", it works, but "priceorder" or "Priceorder" does not work. I tried using the following query using COLLATE, but it does not work. Let me know where I am going wrong.

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

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

Answer 2

See this similar question and answer for case insensitive search - SQL Server ignore case in the where clause

Try using something like:

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

Answer 3

You should SQL_Latin1_General_Cp1_CI_AS_KI_WI use SQL_Latin1_General_Cp1_CI_AS_KI_WI as your sort. The one you specify in your question is clearly case sensitive.

You can see the list of mappings here .