CodesJava

Easy learning with example program codes

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:

SyntaxUSES
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 follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status