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.
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