SQLite like

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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 -

Wildcards Description
% Matches any number of characters including zero.
_ Matches exactly one character.

Syntax :

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

Argument :

Name Description
pat A pattern which is to be matched.

Sample table : author

aut_id      aut_name        country     home_city----------  --------------  ----------  ----------AUT001      William Norton  UK          CambridgeAUT002      William Maugha  Canada      TorontoAUT003      William Anthon  UK          LeedsAUT004      S.B.Swaminatha  India       BangaloreAUT005      Thomas Morgan   Germany     ArnsbergAUT006      Thomas Merton   USA         New YorkAUT007      Piers Gibson    UK          LondonAUT008      Nikolai Dewey   USA         AtlantaAUT009      Marquis de Ell  Brazil      Rio De JanAUT010      Joseph Milton   USA         HoustonAUT011      John Betjeman   Australia   SydneyAUT012      Evan Hayek      Canada      VancouverAUT013      E. Howard       Australia   AdelaideAUT014      C. J. Wilde     UK          LondonAUT015      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, countryFROM authorWHERE LIKE('W%',aut_name)=1;

Here is the output.

aut_name        country--------------  ---------William Norton  UKWilliam Maugha  CanadaWilliam 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, countryFROM authorWHERE LIKE('%on',aut_name)=1;

Here is the output.

aut_name        country--------------  ----------William Norton  UKThomas Merton   USAPiers Gibson    UKJoseph 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 authorWHERE LIKE('%an%',aut_name)=1;

Here is the output.

aut_name                                            country--------------------------------------------------  ----------William Anthony                                     UKS.B.Swaminathan                                     IndiaThomas Morgan                                       GermanyJohn Betjeman Hunter                                AustraliaEvan Hayek                                          CanadaButler 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.

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

Example of SQLite LIKE operator matching escape character

Here is a sample table test.

table - testdescrip---------------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.

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.

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, countryFROM author         WHERE  LIKE('t%n',aut_name);

Here is the output.

aut_name                        country------------------------------  ----------Thomas Morgan                   GermanyThomas 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, countryFROM author         WHERE  LIKE('____________',aut_name);

Here is the output.

aut_name                        country------------------------------  --------Piers Gibson                    UKButler Andre                    USA