SQLite LIKE Operator

SQLite is a serverless architecture that we use to develop embedded software for devices like televisions, cameras, and so on. It is written in C programming Language. It allows the programs to run without any configuration. In this article, we will learn everything about the LIKE operator. After reading this article you will get decent knowledge about the LIKE operator.

LIKE Operator in SQLite

Sometimes it might happen that when we want to find something but we don’t know everything about that thing. But we know partially about it, So here we take leverage of Like Operator. LIKE operator is a pattern-matching operator that is used for matching the specified pattern within the fields. With the help of the LIKE operator, one can easily find the information even if they don’t have complete knowledge about it.

In Simple Words, the SQLite LIKE operator is used to search the values/pattern in specified columns or fields using wildcards

Syntax:

SELECT col1, col2 FROM TABLE WHERE expression  LIKE Condition 

Wildcard Characters

Wildcard characters are the characters that can be used for searching, filtering, a and retrieving data that follow a specific structure. With the help of wildcard characters, we can handle complex queries to fetch results very easily. Here are some wildcard characters that we will use everywhere.

1. Wildcard Character Percent Sign (%)

It is used to match the zero or more number of occurrences in the fields. Lets understand with the examples. Suppose we have created GeekforGeeks Table and we have to find out how many people are work in IT Department so we can simply find out the result with the help of LIKE operator.

GeekforGeeks Table of Employees

Syntax:

SELECT emp_name,emp_dept FROM GeekforGeeks WHERE emp_dept LIKE "IT%"

Explanation: We simply fetch the employees name along with the department name for all employees works in IT departments. In like we have more than one character that’s why we use % with LIKE operator. Result shown below.

The result of all employees works in IT departments

2. Wildcard Character Underscore Sign (_ )

It is used to match one character of the field. We use _ wildcard when we want the character at the exact position. Lets understand with the example in GeekforGeeks table. Suppose we have to find out those employees whose name contain ‘a’ at the second position.

Syntax:

SELECT emp_name,emp_dept FROM GeekforGeeks WHERE emp_name LIKE "_a%"

Explanation: We simply fetch all the employees which contain character ‘a’ at the second position. We can clearly understand that here we have match only one character. Result shown below.

All employees with character ‘a’ at the second position

SQLite with the ESCAPE Clause

Suppose we use Like operator with – or % wildcard and the field also contain _ or % as a character then it is difficult to match wildcard with the same character in the fields. So we overcome this problems by using ESCAPE Clause. ESCAPE Clause helps us to determine the pattern even it contain wildcards as a literals. ESCAPE simply avoid the wildcard characters specified in the condition and gave us the correct output.

Syntax:

SELECT  result FROM table WHERE expression LIKE expression ESCAPE expression

We will understand with the help of example.

SELECT emp_name,emp_dept FROM GeekforGeeks WHERE emp_name LIKE '%\_%' ESCAPE '\'

In this above query we will fetch all employees who have ‘_’ in their name . here ‘_’ is considered as a simple character with the help of ESCAPE Clause which will avoid/escape the _ as a wildcard and return the result.

Result

Explanation: We have two employees with the _ in their name with the help of ESCAPE clause.

Conclusion

So the LIKE operator in SQLite is used to pattern seaching, it allow us to search for specified pattern within the text column. It provides multiple wildcards characters like _, % for making matching easy and very efficiently. We can also use ESCAPE clause with LIKE Operator to overcome the problem of wildcards as a literal. In General one can easily use LIKE operator in different queries with Wildcards according to conditions.

FAQs on SQLite LIKE Operator

1. What is LIKE operator?

Like Operator is a operator that we use for pattern matching into the database.

2. Is LIKE operator Case-Sensitive?

NO it is not case sensitive you can either in uppercase or lowercase manner.

3. Is there a not like operator in SQLite?

Yes, in SQLite, you can use the “NOT LIKE” operator to perform the opposite of a “LIKE” operation.