SQLite LIKE operator

The SQLite LIKE Operator

In SQLite the LIKE operator is used along with WHERE clausing for searching strings in a column againt specified pattern using wildcards.

There are two wildcards which used in simultaneity with the LIKE operator −

The percent sign (%) - represents zero, one, or multiple characters
The underscore (_) - represents a single character.

The underscore and the percent sign may also be used in different combinations!

SQLite LIKE Operator Syntax

In SQLite it is possible to use LIKE operator using the following syntax.


SELECT *
FROM table_name
WHERE column LIKE pattern;


SELECT column_1, column_2, ...
FROM table_name
WHERE column LIKE pattern;

In the above syntax the pattern can be in one or more combinations.

LIKE Operator	Description
WHERE AGE LIKE '2%'	Search and finds the values that starts with "2" e.g. 20 , 21, 26, 29 ..
WHERE AGE LIKE '%6'	Search and finds the values that ends with "6" e.g. 16, 26, 36, 46 ...
WHERE INVENTORY LIKE '%123%'	Search and finds values that contains "123" in any position e.g 1123 1234 41235 ...
WHERE AGE LIKE '_3%'	Search and finds values that contains "3" in the second position e.g. 23, 132, 43
WHERE INVENTORY LIKE '3_%_%'	Search and finds values that starts with "3" and are at least 3 characters in length e.g. 325, 3696, 32457
WHERE INVENTORY LIKE '4%7'	Search and finds values that starts with "4" and ends with "7" e.g. 4557, 427, 42587

Example:

So let's consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
8           Jerry       Bann        20          Velbert

So Let's say we want to find all students whose age is between 20's.

We can use the following SQLite statement:

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

Now when we select the STUDENTS table again, it will look as below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
4           Noor        Khan        22          Bonn
6           Tim         Netten      20          Frankfurt
8           Jerry       Bann        20          Velbert

SqLite Tutorial

SqLite - HOME SqLite - Intro SqLite - Installation SqLite - Syntax SqLite - Data Types SqLite - Create DB SqLite - Create Table SqLite - Drop Table SqLite - Select SqLite - Distinct SqLite - Where SqLite - And & Or SqLite - Order By SqLite - Insert Into SqLite - Update SqLite - Delete SqLite - Injection SqLite - Select Top SqLite - Like SqLite - Wildcards SqLite - In SqLite - Between SqLite - Aliases SqLite - Joins SqLite - Inner Join SqLite - Left Join SqLite - Right Join SqLite - Full Join SqLite - Union SqLite - Select Into SqLite - Insert Into Select SqLite - Constraints SqLite - Not Null SqLite - Unique SqLite - Primary Key SqLite - Foreign Key SqLite - Check SqLite - Default SqLite - Create Index SqLite - Drop SqLite - Alter SqLite - Auto Increment SqLite - Views SqLite - Dates SqLite - Null Values SqLite - Null Functions SqLite - Data Types SqLite - DB Data Types

SqLite Functions

SqLite - Functions SqLite - Avg() SqLite - Count() SqLite - First() SqLite - Last() SqLite - Max() SqLite - Min() SqLite - Sum() SqLite - Group By SqLite - Having SqLite - Ucase() SqLite - Lcase() SqLite - Mid() SqLite - Len() SqLite - Round() SqLite - Now() SqLite - Format()