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.

Please follow and like us:
Content Protection by DMCA.com