SQL- Ignore case while searching for a string

sql sql-server case-insensitive sql-like ignore-case
shockwave picture shockwave · Apr 18, 2013 · Viewed 261k times · Source


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

Aditya Kakirde picture Aditya Kakirde · Apr 18, 2013

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

Related questions

SQL server ignore case in a where expression

How do I construct a SQL query (MS SQL Server) where the "where" clause is case-insensitive? SELECT * FROM myTable WHERE myField = 'sOmeVal' I want the results to come back ignoring the case

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More