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

Possible duplicate of SQL server ignore case in a where expression — Liam
– Liam, Commented Oct 17, 2018 at 9:00

informatik01 · Accepted Answer · 2025-03-05 13:10:43Z

397

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

edited Mar 5 at 13:10

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Apr 18, 2013 at 12:23

Aditya Kakirde

5,3051 gold badge15 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ASA Over a year ago

I think you should always compare strings capitalized (UPPER) as best practice. Google "turkish i"

Shaiju T Over a year ago

Like to know does your answer has any performance issue by converting a column value to UPPER or LOWER case then using the LIKE to search ?

Crusha K. Rool Over a year ago

Actually you would have to compare both UPPER and LOWER variants because some characters have different representations in upper case but the same representation in lower case. For other characters, the opposite may be true. Java mentions the Georgian alphabet specifically as reason for doing an additional toLowerCase() in its case-insensitive comparison.

Jeff S. Over a year ago

Unfortunately, this approach causes a full table scan as described in this post: alvinalexander.com/sql/…. An index search can not be applied, as the filtered column is modified by the UPPER/LOWER function.

Jeff S. Over a year ago

Setting a collation (before creating the index) seems to be the better approach if query performance with upper/lower is not sufficient.

|

Liam · Accepted Answer · 2018-10-17 09:01:38Z

55

Like this.

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

In postgresql.

edited Oct 17, 2018 at 9:01

Liam

30k28 gold badges143 silver badges206 bronze badges

answered Jun 8, 2018 at 5:02

Hangk

5834 silver badges2 bronze badges

2 Comments

Liam Over a year ago

This question is tagged sql-server so a postgres answer isn't relevant.

elgsylvain85 Over a year ago

Thanks. I applied same logic on Apache Spark and works correctly

Community · Accepted Answer · 2017-05-23 12:10:32Z

13

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Apr 18, 2013 at 12:12

Miguel-F

13.6k6 gold badges40 silver badges66 bronze badges

3 Comments

shockwave Over a year ago

it din't work I get SQL query not properly ended before Collate

Jishant Over a year ago

@Miguel-F.. it is working fine but how does it differ from SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME LIKE '%priceorder%' because this is also working fine for me..

YongaJ Over a year ago

I would advice using 'SQL_Latin1_General_Cp1_CI_AS_KI_WI' instead. It's not case sensetive for a seach.

Codemaker2015 · Accepted Answer · 2021-05-18 05:32:31Z

The like operator is not case sensitive in almost all the SQL compilers. If you are still not getting case sensitive results then go with iLike operator. Also you can compare it by changing the case using Upper() method.

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

or

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

score 4 · Accepted Answer · 2013-04-18 12:16:53Z

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.

black_pottery_beauty · Accepted Answer · 2021-05-19 08:30:45Z

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

ReGo · Accepted Answer · 2021-12-01 12:07:20Z

Here's the solution which I use.

SELECT * FROM TABLE WHERE COLUMN ~* '.*search_keyword_any_case.*'

This will also check for substring. Overall the best solution that I came across from the internet. I use the same to implement substring case insensitive search for table's columns.

Arqam Rafay · Accepted Answer · 2022-12-26 08:52:16Z

LIKE and ILIKE allow pattern matching within character-based column data. Their syntax is identical, but LIKE is case-sensitive, while ILIKE is case-insensitive. i am using postgresql, it work fine for me. i.e

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

ouflak · Accepted Answer · 2021-10-10 09:52:14Z

ALTERNATE & BEST SOLUTION:
You can also use a specific "collation" like utf8 > utf8_unicode_ci. Then ALL QUERIES will be insensitive to case. But the data can be in upper case in some places. So just be sure that there is no twin with different cases possible (like using a UNIQUE column to be sure).

QUERY Example:
https://prnt.sc/1vjwxd1
Table design & collation used : https://prnt.sc/1vjx3h5.

"A good design of Database can save you a lot of work in queries" - Me :)

My coalition is utf8_general_ci and the queries are still case sensitive

Collectives™ on Stack Overflow

SQL- Ignore case while searching for a string

9 Answers 9

6 Comments

2 Comments

3 Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

6 Comments

2 Comments

3 Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Linked

Related