SQLite LIKE
To match the text values against a pattern, the SQLite LIKE operator is used. It uses wildcards: the percent sign
Syntax 1:
SELECT FROM table_name WHERE column LIKE 'XXX
Syntax 2:
SELECT FROM table_name WHERE column LIKE
Syntax 3:
SELECT FROM table_name WHERE column LIKE 'XXXX_'
Syntax 4:
SELECT FROM table_name WHERE column LIKE '_XXXX'
Syntax 5:
SELECT FROM table_name WHERE column LIKE '_XXXX_'
Parameters:
XXXX: It is used to represent any numeric or string value.
Different Syntax and their meanings:
| Syntax | USES |
| SELECT FROM table_name
WHERE column LIKE ‘XY | Fetch values that start with XYZ. |
| SELECT FROM table_name
WHERE column LIKE ‘ | Fetch values that have XYZ in any position. |
| SELECT FROM table_name
WHERE column LIKE ‘_X | Fetch values that have XY in the second and third positions. |
| SELECT FROM table_name
WHERE column LIKE ‘X | Fetch values that start with X and are at least 3 characters in length. |
| SELECT FROM table_name
WHERE column LIKE ‘ | Fetch values that end with X. |
| SELECT FROM table_name
WHERE column LIKE ‘_ | Fetch values that have X in the second position and end with Y. |
| SELECT FROM table_name
WHERE column LIKE ‘X___Y’ |
Fetch values in a five-digit number that start with X and end with Y. |
Example:
TEACHERS Table:
| ID | NAME | AGE | SUBJECT |
| 1 | Jim | 27 | English |
| 2 | John | 30 | Geology |
| 3 | Watson | 28 | French |
| 4 | Holmes | 40 | Chemistry |
| 5 | Tony | 35 | Physics |
SELECT * FROM TEACHERS WHERE NAME LIKE ‘%o%’; |
Output:
| ID | NAME | AGE | SUBJECT |
| 2 | John | 30 | Geology |
| 3 | Watson | 28 | French |
| 4 | Holmes | 40 | Chemistry |
| 5 | Tony | 35 | Physics |
Explanation:
The “TEACHERS” is an already existing table. Here we are selecting those rows of the table where the value in the NAME column have ‘o’ inside the text.