YouTipSqlite Like Clause
# SQLite Like Clause
SQLite's **LIKE** operator is used to match text values against a pattern specified by wildcards. If the search expression matches the pattern expression, the LIKE operator returns true, which is 1. There are two wildcards used in conjunction with the LIKE operator:
* Percent sign (%)
* Underscore (_)
The percent sign (%) represents zero, one, or multiple numbers or characters. The underscore (_) represents a single number or character. These symbols can be combined.
## Syntax
The basic syntax of % and _ is as follows:
SELECT column_list FROM table_name WHERE column LIKE 'XXXX%'or SELECT column_list FROM table_name WHERE column LIKE '%XXXX%'or SELECT column_list FROM table_name WHERE column LIKE 'XXXX_'or SELECT column_list FROM table_name WHERE column LIKE '_XXXX'or SELECT column_list FROM table_name WHERE column LIKE '_XXXX_'
You can combine N number of conditions using AND or OR operator. Here, XXXX can be any number or string value.
## Examples
The following examples demonstrate different places where the LIKE operator with '%' and '_' operators can be used:
| Statement | Description |
| --- | --- |
| WHERE SALARY LIKE '200%' | Finds any value that starts with 200 |
| WHERE SALARY LIKE '%200%' | Finds any value that has 200 in any position |
| WHERE SALARY LIKE '_00%' | Finds any value that has 00 in the second and third positions |
| WHERE SALARY LIKE '2_%_%' | Finds any value that starts with 2 and is at least 3 characters in length |
| WHERE SALARY LIKE '%2' | Finds any value that ends with 2 |
| WHERE SALARY LIKE '_2%3' | Finds any value that has 2 in the second position and ends with 3 |
| WHERE SALARY LIKE '2___3' | Finds any value that is 5 characters long, starts with 2, and ends with 3 |
Let us take a real example, suppose COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0
Here is an example, which displays all the records from the COMPANY table where the AGE starts with 2:
sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';
This would produce the following result:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------2 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0
Here is another example, which displays all the records from the COMPANY table where the ADDRESS text contains a hyphen (-):
sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
This would produce the following result:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------4 Mark 25 Rich-Mond 65000.06 Kim 22 South-Hall 45000.0