CODEWITHSUNDEEP
sqlsqlite
tpcolson
tpcolson

Reputation: 677

Case-sensitive wildcard query not working in SQLite

I am trying to query a case-sensitive string in SQLite using wildcard such that no matter where it occurs (beginning, middle, or end of column value which contains other strings) it will return the string.

For example search for 'ALBI' should return

but not

None of the following return exactly 'ABLI'. They either return some, none, or everything but nothing where the string is EXACLY 'ABLI' with wild card on either side.

SELECT title
FROM INVENTORY_TEST
--WHERE UPPER (title) LIKE '%ABLI%' --Returns all 21 rows
--WHERE title LIKE '%ABLI%' --Returns all 21 rows
--WHERE title LIKE 'ABLI' -- Returns 0 rows
--WHERE title REGEXP '\bABLI\b' -- Returns 4 rows only where ALBI has space on either side
--WHERE title REGEXP '%\bABLI\b%' -- Returns 0 rows
--WHERE title REGEXP '\b%ABLI%\b' -- Returns 0 rows
--WHERE UPPER(title) GLOB UPPER('%ABLI%') -- Returns 0 rows
--WHERE title Like ('%ABLI%') COLLATE NOCASE --Returns all 21 rows
--WHERE title REGEXP '^[ABLI]' --Returns words with 'abli' string in them
--WHERE title LIKE '%[^A-Za-z]ABLI[^A-Za-z]%' -- Returns 0 rows

And yes I've tried setting the Collation on the column definition to NOCASE and BINARY. No change in any of the queries above.

Upvotes: 1

Views: 32

Answers (1)

samhita
samhita

Reputation: 3490

You can use GLOB which is case-sensitive. As per documentation

The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE

SELECT TITLE
FROM INVENTORY_TEST
WHERE TITLE GLOB '*ABLI*';

returns

ABLI word word
ABLI_Edits_20240611
word ABLI
word_ABLI
string ABLIword

for input data

ABLI word word
ABLI_Edits_20240611
word ABLI
word_ABLI
string ABLIword
BotanyTransectEstablishment
ABlitz_OfficeMap
BotanyTransectEstaBLIshment_pt
Extra

Fiddle Demo

Upvotes: 2

Related Questions