SQLite like() function

Description

The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator.

Note: The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, a different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.

SQLite LIKE operator along with WILDCARDS finds a string of a specified pattern within another string.

In a more technical note, LIKE operator does pattern matching using simple regular expression comparison.

This is a table which describes the wildcards used with SQLite LIKE operator -

like(X,Y)like(X,Y,Z)

Sample table: author

aut_id      aut_name        country     home_city
----------  --------------  ----------  ----------
AUT001      William Norton  UK          Cambridge
AUT002      William Maugha  Canada      Toronto
AUT003      William Anthon  UK          Leeds
AUT004      S.B.Swaminatha  India       Bangalore
AUT005      Thomas Morgan   Germany     Arnsberg
AUT006      Thomas Merton   USA         New York
AUT007      Piers Gibson    UK          London
AUT008      Nikolai Dewey   USA         Atlanta
AUT009      Marquis de Ell  Brazil      Rio De Jan
AUT010      Joseph Milton   USA         Houston
AUT011      John Betjeman   Australia   Sydney
AUT012      Evan Hayek      Canada      Vancouver
AUT013      E. Howard       Australia   Adelaide
AUT014      C. J. Wilde     UK          London
AUT015      Butler Andre    USA         Florida

Example of SQLite LIKE operator with wildcard (%) matching from the beginning

The following SQLite statement will return those rows from the table author in which the name of the author starts with the character ‘W’.

SELECT aut_name, country
FROM author
WHERE LIKE('W%',aut_name)=1;

Here is the output.

Sample Output:

aut_name        country
--------------  ---------
William Norton  UK
William Maugha  Canada
William Anthon  UK

Example of SQLite LIKE operator with wildcard (%) matching from the end

The following SQLite statement will return those rows from the table author in which the name of the author ends with the substring ‘on’.

SELECT aut_name, country
FROM author
WHERE LIKE('%on',aut_name)=1;

Here is the output.

Sample Output:

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

Example of SQLite LIKE operator with wildcard (%) matching within the string

The following SQLite statement will return those rows from the table author in which the name of the author contains ‘an’.

SELECT aut_name, country 
FROM author
WHERE LIKE('%an%',aut_name)=1;

Here is the output.

Sample Output:

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

Example: SQLite LIKE operator matching a specified string

The following MySQL statement searches all authors whose home city are such as ‘London’, ’Landon’ etc. the underscore wildcard is used to mention single character.

SELECT aut_name, country,home_city       
FROM author        
WHERE LIKE('L_n_on',home_city)=1;

Here is the output.

Sample Output:

aut_name                        country     home_city
------------------------------  ----------  ----------
Piers Gibson                    UK          London
C. J. Wilde                     UK          London

Example of SQLite LIKE operator matching escape character

Here is a sample table test.

Sample Output:

table - test
descrip
---------------
w3resourcew3%resourcew3r%e_sourcew3r_esourcew3r%__esource

In SQLite the default ESCAPE string is "\". The following SQLite statement returns those records, whose descrip column contain r%'.

SELECT * 
FROM test 
WHERE LIKE('%r\%%',descrip,'\')=1;

Here is the output.

Sample Output:

descrip
-----------------
w3r%e_sourcew3r%__esource

In the above example the ESCAPE character '\' have been used after LIKE('%r , after that two % symbol have been used, the first one is searching character and the second one is for pattern matching character. The third parameter in the like function the ESCAPE character have been used for searching a wild card character %.

Here is another example

SELECT * 
FROM test 
WHERE LIKE('%\_e%',descrip,'\')=1;

Here is the output.

Sample Output:

descrip
----------------
w3r_esourcew3r%__esource

In the above example the ESCAPE character '\' have been used after LIKE('% , after that the searching character underscore( _ ) and the last % symbol is for pattern matching character. The third parameter in the like function, the ESCAPE character have been used for searching a wild card character underscore ( _ ).

Example of SQLite LIKE operator matching beginning and ending string

Wildcards can also be used in the middle of a search pattern. The following SQLite statement will find all authors whose name begin with a ‘t’ and end with a ‘n’.

SELECT aut_name, country
FROM author         
WHERE  LIKE('t%n',aut_name);

Here is the output.

Sample Output:

aut_name                        country
------------------------------  ----------
Thomas Morgan                   Germany
Thomas Merton                   USA 

Example of SQLite LIKE operator matching exact number of characters

The following SQLite statement will return those rows from the table author in which the length of the author’s name is exactly 12 characters. Twelve ‘_’ have been used to indicate 12 characters.

SELECT aut_name, country
FROM author         
WHERE  LIKE('____________',aut_name);

Here is the output.

Sample Output:

aut_name                        country
------------------------------  --------
Piers Gibson                    UK
Butler Andre                    USA

Previous: length()
Next: lower()