Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

I have the following data in a Table 

PriceOrderShipped 

PriceOrderShippedInbound 

PriceOrderShippedOutbound 

In SQL I need to write a query that 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 I'm going wrong.

SELECT DISTINCT COL_NAME FROM myTable WHERE 

COL_NAME COLLATE latin1_general_cs LIKE '%Priceorder%'

1 Answer

0 votes
by (40.7k points)

Try using the below code:

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

Otherwise, you can use this code:

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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Nov 21, 2020 in SQL by Appu (6.1k points)

Browse Categories

...