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.
Please follow and like us: