SQLite LIKE Clause

SQLite'sLIKEoperator is used to match text values against a pattern specified by wildcards. If the search expression matches the pattern expression, the LIKE operator returns true, i.e., 1. Here are two wildcards used with the LIKE operator:

  • Percent sign (%)

  • Underscore (_)

The percent sign (%) represents zero, one, or multiple numbers or characters. The underscore (_) represents a single number or character. These symbols can be combined.

Syntax

The basic syntax of % and _ is as follows:

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'

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

Examples

The following examples demonstrate the differences of the LIKE clause with '%' and '_' operators:

StatementDescription
WHERE SALARY LIKE '200%'Find any value that starts with 200
WHERE SALARY LIKE '%200%'Find any value that contains 200 at any position
WHERE SALARY LIKE '_00%'Find any value where the second and third positions are 00
WHERE SALARY LIKE '2_%_%'Find any value that starts with 2 and is at least 3 characters long
WHERE SALARY LIKE '%2'Find any value that ends with 2
WHERE SALARY LIKE '_2%3'Find any value where the second position is 2 and ends with 3
WHERE SALARY LIKE '2___3'Find any value that is 5 digits long, starts with 2, and ends with 3

Let us take a practical example. Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       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     10000.0

The following is an example that displays all records in the COMPANY table where AGE starts with 2:

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

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       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     10000.0

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

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

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
Other Extensions