query with LIKE but case sensitive

Question

Why isn't my query case sensitive?

Select * from MyTable where name like '%Ann%'

shows record 1 correctly:

Record1= John, Ann, Jack

but shows also record 2:

Record2: Jack, Susanne, Jim

score 4 · Accepted Answer · answered Mar 10 '14 at 21:04

You could execute PRAGMA case_sensitive_like = on, but this would affect all LIKEs used in your program, and disable any index optimizations for prefix searches.

A better idea would be to replace LIKE with GLOB:

SELECT * FROM MyTable WHERE Name GLOB '*Ann*'

score -2 · Answer 2 · edited May 23 '17 at 12:30

-2

the SQL LIKE is case-insensitive(thnks to CL ) So, on some SQL implementations you can do case Sensitive SQL query Searches

check this too: Case insensitive searching in Oracle

edited May 23 '17 at 12:30

Community

1
1

answered Mar 10 '14 at 20:51

Caffeinated

11,982
40
122
216

Is Sqlite caseinsensitive too? – lucignolo Mar 10 '14 at 21:12
1

LIKE is case insensitive as specified in the SQL standard. To change this default, you have to use a database-specific mechanism. – CL. Mar 10 '14 at 21:14

score -2 · Answer 3 · edited May 23 '17 at 11:59

-2

[Copied from rbedger's answer:]

You can use the UPPER keyword on your case insensitive field then upper-case your like statement

SELECT * FROM mytable 
WHERE caseSensitiveField like 'test%' 
AND UPPER(caseInsensitiveField) like 'G2%'

edited May 23 '17 at 11:59

Community

1
1

answered Mar 10 '14 at 21:09

Martin

4,711
4
29
37

2

Using UPPER does not make the comparison case *sensitive*. – CL. Mar 10 '14 at 21:19

query with LIKE but case sensitive

3 Answers3

Related