SQLite LIKE

The SQLite LIKE operator is used to match a specific pattern. If the pattern matches, then the query returns rows.

Syntax

The SQLite LIKE syntax is as follows:

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name LIKE pattern;

Example

Table of student_address

address_id	city	country
100	San Antonio	US
101	San Jose	US
102	Philadelphia	US
103	Austin	US
104	Boston	US
105	Seattle	US

In the first example, the select returns rows for the city that contains the expression ‘to’.

SELECT * FROM student_address WHERE city LIKE '%to%';

Output

address_id	city	country
100	San Antonio	US
104	Boston	US

The second select returns records if the city name begins with the phrase ‘San’.

SELECT * FROM student_address WHERE city LIKE 'San%';

Output

address_id	city	country
100	San Antonio	US
101	San Jose	US

In the third example, the select returns the rows where the city name ends with ‘ttle’.

SELECT * FROM student_address WHERE city LIKE '%ttle';

Output

address_id	city	country
105	Seattle	US

Also the LIKE operator can be used to return rows for a specified expression without using ‘%’.

SELECT * FROM student_address WHERE city LIKE 'Austin';

Output

address_id	city	country
103	Austin	US