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](https://stackoverflow.com/questions/1224364/sql-server-ignore-case-in-a-where-expression) — Liam, Oct 17 '18 at 09:00

score 369 · Accepted Answer · edited Apr 18 '13 at 12:43

369

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 Apr 18 '13 at 12:43

fedorqui

275,237
103
548
598

answered Apr 18 '13 at 12:23

Aditya Kakirde

4,935
1
13
10

27

I think you should always compare strings capitalized (UPPER) as best practice. Google "turkish i" – ASA Apr 18 '14 at 18:27
4

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 ? – Shaiju T Aug 29 '18 at 09:27
1

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. – Crusha K. Rool Mar 04 '19 at 08:52
5

Unfortunately, this approach causes a full table scan as described in this post: https://alvinalexander.com/sql/sql-select-case-insensitive-query-queries-upper-lower. An index search can not be applied, as the filtered column is modified by the UPPER/LOWER function. – Jeff S. Jul 12 '19 at 15:56
Setting a collation (before creating the index) seems to be the better approach if query performance with upper/lower is not sufficient. – Jeff S. Jul 16 '19 at 08:40
Is the LIKE operator case sensitive in MySQL?. In my understanding it is not. – Indika K Jul 09 '22 at 23:28

score 47 · Answer 2 · edited Oct 17 '18 at 09:01

47

Like this.

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

In postgresql.

edited Oct 17 '18 at 09:01

Liam

27,717
28
128
190

answered Jun 08 '18 at 05:02

Hangk

503
4
2

10

This question is tagged [tag:sql-server] so a postgres answer isn't relevant. – Liam Oct 17 '18 at 09:02
Thanks. I applied same logic on Apache Spark and works correctly – elgsylvain85 Jul 01 '22 at 19:33

score 12 · Answer 3 · edited May 23 '17 at 12:10

12

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 '17 at 12:10

Community

1
1

answered Apr 18 '13 at 12:12

Miguel-F

13,450
6
38
63

it din't work I get SQL query not properly ended before Collate – shockwave Apr 18 '13 at 12:23
@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.. – Jishant Jun 13 '16 at 09:16
I would advice using 'SQL_Latin1_General_Cp1_CI_AS_KI_WI' instead. It's not case sensetive for a seach. – YongaJ Aug 07 '19 at 13:21

score 4 · Answer 4 · 2013-04-18T12:16:53.200

4

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.

edited Apr 18 '13 at 12:16

answered Apr 18 '13 at 12:10

score 3 · Answer 5 · answered May 18 '21 at 05:32

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 1 · Answer 6 · answered May 19 '21 at 08:30

1

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

answered May 19 '21 at 08:30

black_pottery_beauty

861
8
17

score 1 · Answer 7 · answered Dec 01 '21 at 12:07

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.

score 0 · Answer 8 · edited Oct 10 '21 at 09:52

0

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 :)

edited Oct 10 '21 at 09:52

ouflak

2,458
10
44
49

answered Oct 10 '21 at 09:03

Vinik

1
1

My coalition is utf8_general_ci and the queries are still case sensitive – Twiggit Jun 07 '22 at 17:36

score 0 · Answer 9 · answered Dec 26 '22 at 08:52

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

SQL- Ignore case while searching for a string

9 Answers9

Linked