GLOB in SQLite

SQLite GLOB
To match the text values against a pattern, the SQLite GLOB operator is used. It uses wildcards: the asterisk sign (*) representing 0 or multiple numbers or characters; and the question mark (?) representing a single number or character.

Syntax 1:

SELECT FROM table_name  
WHERE column GLOB 'XXXX*'

Syntax 2:

SELECT FROM table_name  
WHERE column GLOB '*XXXX*'

Syntax 3:

SELECT FROM table_name  
WHERE column GLOB 'XXXX?'

Syntax 4:

SELECT FROM table_name  
WHERE column GLOB '?XXXX'

Syntax 5:

SELECT FROM table_name  
WHERE column GLOB '?XXXX?'

Syntax 6:

SELECT FROM table_name  
WHERE column GLOB '????'

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 GLOB ‘XYZ*’	Fetch values that start with XYZ.
SELECT FROM table_name WHERE column GLOB ‘XYZ’	Fetch values that have XYZ in any position.
SELECT FROM table_name WHERE column GLOB ‘?XY*’	Fetch values that have XY in the second and third positions.
SELECT FROM table_name WHERE column GLOB ‘X??’	Fetch values that start with X and are at least 3 characters in length.
SELECT FROM table_name WHERE column GLOB ‘*X’	Fetch values that end with X.
SELECT FROM table_name WHERE column GLOB ‘?X*Y’	Fetch values that have X in the second position and end with Y.
SELECT FROM table_name WHERE column GLOB ‘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 GLOB ‘*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 in any position.