SQLite LIKE Operator

  Total Views : 37
  Comments : 0
 
  Keywords : SQLite like clause with example, SQLite like condition with select statement example, Use sqlite like with % & _ characters example, SQLite like operator to fetch data with pattern matching

Here we will learn sqlite like operator with example and how to use sqlite like operators (%, _) to match text values against pattern in select statement with examples.

SQLite Like Operator

In SQLite LIKE operator is used to check whether the given string value matching 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. 

Syntax of SQLite LIKE Operator

Following is the Syntax of SQLite LIKE operator with select statement.

 

SELECT result

FROM table_name

WHERE expression LIKE pattern

In above SQLite LIKE syntax, we defined few properties with LIKE operator in 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. 

 

  • % allows you to match any string of any length (including zero length).
  • _  allows you to match on a single character.

We will see how to use SQLite LIKE operator with example for that create emp_master table and insert some data in it using 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

SQLite LIKE Operator with % Wildcard

Following is the example of using SQLite LIKE operator with wild character “%” in select statement to get employees whose first name starts with 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 return employees whose first name starts with ‘s’ or ‘S’ character.

 

Now we will write SQLite LIKE operator query with select statement to get employees whose name contains 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 select statement to get employee details whose name ends with 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.

SQLite LIKE Operator with _ Wildcard

Following is the example of using SQLite LIKE operator with “_” wildcard in 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 return employees whose last name has 4 characters and starts with ‘ran’.

 

Following details give idea to use SQLite Like operator with wildcard characters %, _ to get records from tables.

 

ExpressionsDescription
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 second character as ‘t’ and it should contain minimum 2 characters

This is how we can use SQLite Like operator to get details from tables based on our requirements.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User