Sqlite Like % and _

In the Wild of Special Characters

To search for % or _ as literal characters, use the ESCAPE keyword.

SELECT * FROM table WHERE column LIKE '%25%' ESCAPE '25';    -- Seeking '%' in its natural habitat
SELECT * FROM table WHERE column LIKE '!_%' ESCAPE '!';      -- Hunting for '_', no camouflage allowed!


Just remember, our escape characters here are as freely chosen as a Netflix movie on a lazy Sunday!

Multi Underscore and Percentage Saga

One underscore (_), two underscore (__), red underscore, blue underscore! Multiple underscores (___) match a definitive set of characters while multiple % (%%%) simply plays the part of that elusive wildcard, indifferent to the length:

SELECT * FROM table WHERE column LIKE '___abc%';    -- Searching for triplets holding hands with 'abc!


Case of the GLOB Patterns

In SQLite, GLOB comes into the picture for case-sensitive globetrotting:

SELECT * FROM table WHERE column GLOB 'Data*';    -- Could be 'Data Science', or 'Database', or 'Dataphobia'
SELECT * FROM table WHERE column GLOB '?ata';     -- It's our friend '_', but more cat-like (think '?')


Where * stars as the wildcard and ? questions a single character.

No One Puts Case Sensitivity in a Corner

SQLite's LIKE operator throws case out of the window. In contrast, GLOB is the polite detective, always minding its case:

SELECT * FROM table WHERE column LIKE 'a%' COLLATE NOCASE;    -- Case-blind
SELECT * FROM table WHERE column GLOB 'A*';                   -- Case-keen


Pattern Matching: The Master Class

SQLite supports pattern matching beyond basic characters. Check out this intricate LIKE query:

SELECT * FROM table WHERE column LIKE 'C_%_d';    -- Complex much? 'C', any character, any character, and 'd', walking into a bar...


This query uncovers strings that commence with C, succeeded by any two characters and rounded off neatly with a d.