SQLite LIKE operator

Introduction

SQLite LIKE operator checks whether a specific character string matches a specified pattern. The operand to the right of the LIKE operator contains the pattern and the left-hand operand contains the string to match against the pattern.

• A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters in the string.
• An underscore ("_") in the LIKE pattern matches any single character in the string.
• Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters by default.
• The LIKE operator is case sensitive by default for Unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)

Syntax:

expr LIKE pattern [ESCAPE 'escape_char']

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively.

The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the function glob(Y,X) and can be modified by overriding that function.

SQLite Version: 3.8

Example: SQLite LIKE operator

The following SQLite statement scans the whole author table to find any author name which has a first name starting with character ‘B’ followed by any characters.

Sample table: author

SELECT aut_name, country
FROM author
WHERE aut_name LIKE 'B%';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

aut_name      country
------------  ----------
Butler Andre  USA

Example: SQLite LIKE operator matching to end

The following SQLite statement scans the whole author table to find any author which have the name ended with 'on' string.

Sample table: author

SELECT aut_name, country
FROM author
WHERE aut_name LIKE '%on';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

aut_name        country
--------------  ----------
William Norton  UK
Thomas Merton   USA
Piers Gibson    UK
Joseph Milton   USA

Example: SQLite LIKE operator matching within the string

The following SQLite statement scans the whole author table to find any author which have a string ‘an’ in his name. Name of the author is stored in aut_name column.

Sample table : author

SELECT aut_name, country
FROM author
WHERE aut_name LIKE '%an%';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

aut_name         country
---------------  ----------
William Anthony  UK
S.B.Swaminathan  India
Thomas Morgan    Germany
John Betjeman H  Australia
Evan Hayek       Canada
Butler Andre     USA

Example: SQLite LIKE operator matching a specified string

The following SQLite statement searches all authors whose home city are such as 'Leeds', 'Laeds' etc. the underscore wildcard is used to mention single character.

Sample table: author

SELECT aut_name, country,home_city
FROM author
WHERE home_city LIKE 'L_e_s';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

aut_name         country     home_city
---------------  ----------  ----------
William Anthony  UK          Leeds

Example: SQLite LIKE operator matching escape character

To search a wildcard character or a combination of a wildcard character and any other character, the wildcard character must be preceded by an ESCAPE string. In SQLite, the default ESCAPE string is "\". The following SQLite statement returns those records, whose isbn_no contain '15'.

Sample table: book_mast

SELECT book_name,isbn_no,no_page,book_price
FROM book_mast
WHERE isbn_no LIKE '%15%';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

book_name             isbn_no     no_page     book_price
--------------------  ----------  ----------  ----------
Anatomy & Physiology  0000979015  225         135

Example: SQLite LIKE operator matching beginning and ending string

Wildcards can also be used in the middle of a search pattern. The following SQLite statement above will find all authors whose name begin with a 'w' and end with a 'y'.

SELECT aut_name, country
FROM author
WHERE aut_name LIKE 'w%y';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

aut_name         country
---------------  ----------
William Anthony  UK

Sample table: author

Previous: Boolean Operators
Next: BETWEEN Operator