GLOB Clause (Operator)

The SQLite GLOB operator matches only text values against a pattern by using wildcards. When the search expression is matched with the pattern expression, the GLOB operator will return true which is 1.

The GLOB operator follows syntax of UNIX for specifying THE following wildcards.

• The asterisk sign (*): This sign represents zero or multiple numbers or characters
• The question mark (?): This sign represents a single number or character.

Syntax:

Syntax for asterisk sign:

  SELECT FROM table_name  

WHERE column GLOB 'XXXX*'  

1. or   

SELECT FROM table_name  

WHERE column GLOB '*XXXX*'

Syntax for question mark:

SELECT FROM table_name  

WHERE column GLOB 'XXXX?'  

or  

SELECT FROM table_name  

WHERE column GLOB '?XXXX'  

or  

SELECT FROM table_name  

WHERE column GLOB '?XXXX?'  

or  

SELECT FROM table_name  

WHERE column GLOB '????'   

Example:

We have a table named “STUDENT” having following data:

In these examples the WHERE statement having different BLOB clause with ‘*’ and ‘?’ operators:

Statement	Description
WHERE FEES GLOB ‘200*’	Finds any values that start with 200
WHERE FEES GLOB ‘*200*’	Finds any values that have 200 in any position
WHERE FEES GLOB ‘?00*’	Finds any values that have 00 in the second and third positions
WHERE FEES GLOB ‘2??’	Finds any values that start with 2 and are at least 3 characters in length
WHERE FEES GLOB’*2′	Finds any values that end with 2
WHERE FEES GLOB ‘?2*3’	Finds any values that have a 2 in the second position and end with a 3
WHERE FEES GLOB ‘2???3’	Finds any values in a five-digit number that start with 2 and end with 3

Example1:

Select all records from “STUDENT” table where AGE start with 2:

SELECT * FROM STUDENT WHERE AGE  GLOB '2*';   

Output:

Example2:

Select all records from table “STUDENT” where FEES start with 2:

SELECT * FROM STUDENT WHERE FEES  GLOB '2*';   

Output: