LIKE operator in SQLite

SQLite LIKE
To match the text values against a pattern, the SQLite LIKE operator is used. It uses wildcards: the percent sign (%) representing 0, 1, or multiple numbers or characters; and the underscore (_) representing a single number or character.

Syntax 1:

SELECT FROM table_name  
WHERE column LIKE 'XXXX%'  

Syntax 2:

SELECT FROM table_name  
WHERE column LIKE '%XXXX%'  

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 ‘XYZ%’

Fetch values that start with XYZ.
SELECT FROM table_name

WHERE column LIKE ‘%XYZ%’

Fetch values that have XYZ in any position.
SELECT FROM table_name

WHERE column LIKE ‘_XY%’

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 ‘%X’

Fetch values that end with X.
SELECT FROM table_name

WHERE column LIKE ‘_X%Y’

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 Share