Here we will learn SQLite like operator with example and how to use SQLite like operators (%, _) to match text values against the pattern in a select statement with examples.
In SQLite, LIKE operator is used to check whether the given string value matches with specific pattern or not. In case if string value matches with pattern value then it will return true.
In SQLite LIKE operator is a case insensitive, so 'a' LIKE 'A' is true and all non-NULL parameter expressions will be converted to text values.
Following is the Syntax of SQLite LIKE operator with a select statement.
SELECT result
FROM table_name
WHERE expression LIKE pattern
In above SQLite LIKE syntax, we defined few properties with a LIKE operator in a select statement those are
expression - A character expression such as a column or field.
pattern - A character expression that contains pattern matching.
In SQLite LIKE operator pattern syntax supports two wildcards.
We will see how to use SQLite LIKE operator with an example for that create emp_master table and insert some data in it using the following queries.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,3),
(9,'Shwets','Jariwala', 19400,2);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Following is the example of using SQLite LIKE operator with wild character “%” in the select statement to get employees whose first name starts with a particular character.
sqlite> SELECT * FROM emp_master WHERE first_name LIKE 's%';
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
9 Shwets Jariwala 19400 2
The above SQLite LIKE operator example returns employees whose first name starts with ‘s’ or ‘S’ character.
Now we will write SQLite LIKE operator query with a select statement to get employees whose name contains the particular character.
sqlite> SELECT * FROM emp_master WHERE last_name LIKE '%n%';
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
The above SQLite LIKE operator example return employees whose last_name contains ‘n’ or ‘N’ character.
Following is the example of using SQLite LIKE operator in the select statement to get employee details whose name ends with a particular character.
sqlite> SELECT * FROM emp_master WHERE first_name LIKE '%i';
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
4 Jagruti Viras 9500 2
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
If you observe above SQLite LIKE operator example it returns employees whose first_name ends with ‘i’ character.
Following is the example of using SQLite LIKE operator with “_” wildcard in the select statement to get employee details whose name starts with particular characters.
sqlite> SELECT * FROM emp_master WHERE last_name LIKE 'ran_';
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
5 Shweta Rana 12000 3
If you observe SQLite LIKE Operator result it returns employees whose last name has 4 characters and starts with ‘ran’.
The following details give an idea to use SQLite Like operator with wildcard characters %, _ to get records from tables.
| Expressions | Description |
|---|---|
| Where name LIKE ‘t%’ | It returns all the users whose name starts with ‘t’ |
| Where name LIKE ‘%t’ | It returns all the users whose name ends with ‘t’ |
| Where name LIKE ‘%t%’ | It returns all the users whose name contains ‘t’ character |
| Where name LIKE ‘tut_’ | It returns users whose name starts with ‘tut’ and contains 4 characters |
| Where name LIKE ‘_t%’ | It returns users whose name contains the second character as ‘t’ and it should contain a minimum of 2 characters |
This is how we can use SQLite Like operator to get details from tables based on our requirements.