SQLite | Querying Data | Pattern Matching with LIKE

Tags:

LIKE matches text patterns using % and _.

SELECT columns FROM table_name WHERE column LIKE pattern;

Character	Meaning
`%`	Zero or more arbitrary characters
`_`	Exactly one arbitrary character

For example, a%b matches ab, axb, and aoneb; T% matches strings beginning with T; and a_b matches axb but not ab. SQLite LIKE is case-insensitive for ASCII letters by default.

create table user (id integer, name text, address text);
insert into user values (1, 'devkuma', 'Seoul');
insert into user values (2, 'kimkc', 'Busan');
insert into user values (3, 'araikuma', 'Paju');
insert into user values (4, 'happykuma', 'Paju');
insert into user values (5, 'mykuma', 'Daejeon');
insert into user values (6, 'yourkuma', 'Seongnam');
insert into user values (7, 'raccoon', 'Suwon');
select * from user where address like 'S%';
select * from user where address like '%e%e%';
select * from user where name like '_______';
select * from user where name not like '_______';

The queries find addresses beginning with S, addresses containing two e characters, seven-character names, and names that are not seven characters long.

Escape special characters

Use ESCAPE when % or _ should be treated literally.

SELECT columns FROM table_name
WHERE column LIKE pattern ESCAPE escape_character;

create table foods (id integer, name text);
insert into foods values (1, 'Water');
insert into foods values (2, 'Apple_Pie');
insert into foods values (3, 'Black_Coffee');
insert into foods values (4, 'Pizza');
insert into foods values (5, 'Sandwich');
insert into foods values (6, 'French_Bread');
select * from foods where name like '%$_%' escape '$';

The final query uses $ as the escape character and returns the three names containing a literal underscore.