1

So I have the following query:

select username from users where username like 'aDam%'

but the problem here is that its not case sensitive. this query will get also users named 'adam%', 'Adam%', etc..

Question: How do I get only 'aDam%' (make the query case sensitive)?

note: the % operator is important here.

FlyingNades
  • 432
  • 3
  • 16

2 Answers2

5

Before running the query you could set the case_sensitive_like pragma to ON.

PRAGMA case_sensitive_like = boolean;

The default behavior of the LIKE operator is to ignore case for ASCII characters. Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma installs a new application-defined LIKE function that is either case sensitive or insensitive depending on the value of the case_sensitive_like pragma. When case_sensitive_like is disabled, the default LIKE behavior is expressed. When case_sensitive_like is enabled, case becomes significant. So, for example, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.

This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. This pragma only changes the behavior of the SQL LIKE operator. It does not change the behavior of the sqlite3_strlike() C-language interface, which is always case insensitive.

Then just run the query as it is. 

PRAGMA case_sensitive_like=ON;
select username from users where username like 'aDam%';

Test it here on Sql Fiddle.

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • I like this as it's very explicit that you want the query to be case sensitive on this particular element and it doesn't have the overhead of complicating the SQL. – xQbert Sep 30 '18 at 15:04
  • @xQbert The interesting thing is that this also works for a LIKE when the source varchar has a case-insensitive collation. But if you use `=` you then you might have to add `COLLATE BINARY` to the search string. – LukStorms Sep 30 '18 at 15:07
2

Since Like is case insensitive it will not work on its own but one workaround is to use it together with the SUBSTR function 

select username 
from users 
where username like 'aDam%' 
  and substr(username,1,4) = 'aDam'
Joakim Danielson
  • 43,251
  • 5
  • 22
  • 52