SQLite Like clause

SQLite's LIKE operator is used to match text values in patterns specified by wildcards. The LIKE operator returns true, which is 1, if the search expression matches the pattern expression. There are two wildcard characters used with the LIKE operator:

Percent sign (%)
Underscore (_)

The percent sign (%) represents zero, one or more numbers or characters. An underscore (_) represents a single number or character. These symbols can be used in combination.

Syntax

% The basic syntax of _ is as follows:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

You can use the AND or OR operator to combine N quantities of conditions. Here, XXXX can be any number or string value.

Examples

The following examples demonstrate the differences in LIKE clauses with '%' and '_' operators:

##WHERE SALARY LIKE '200%'Find any value starting with 200WHERE SALARY LIKE '%200%'Find any value containing 200 at any positionWHERE SALARY LIKE '_00%'Find any value whose second and third digits are 00WHERE SALARY LIKE '2_%_%'Look for any value that starts with 2 and has a length of at least Any value of 3 charactersWHERE SALARY LIKE '%2'Find any value ending with 2 WHERE SALARY LIKE '_2%3'Find any value whose second digit is 2 and ends with 3##WHERE SALARY LIKE '2___3'

Let's give us a practical example. ----- ------------ ------------ ----------

1 15000.0

3 Teddy 23 NORWAY 20000.0 ## 4 Mark 25 Rich-Mond 65000.0

5 David 27 Texas 85000.0
6 KIM 22 SOUTH-Hall 45000.0
7 James 24 HOUSTON 1000000.0

The following is an example, which displays all records in the COMPANY table whose AGE starts with 2:

sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';

This will produce the following results:

ID ------ ---------- ----------
2 15000.0 ’ ’ ’ ’ ‐ to -- -- - t Mark 25 Rich- Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

The following is an example that displays the ADDRESS text in the COMPANY table All records containing a hyphen (-):

##sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';

This will produce the following results:

ID ------- ----------

4 but

← SQLite Delete statement

SQLite Glob clause →

submit