SQLite Tutorial SQLite Advanced SQLite Database SQLite References

SQLite - GLOB Operator



The SQLite GLOB operator is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the GLOB operator are given below:

Note: The NOT GLOB operator is the negation of GLOB operator.

Note: Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying the given wildcards.

Wildcard Characters in SQLite

SymbolDescriptionExample
*Represents zero, one or multiple characters.'J*' represents a value that start with "J", for example - John, Jo and Jack etc.
?Represents one character.'?o*' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.

Syntax

The syntax for using GLOB operator in SQLite is given below:

SELECT column1, column2, ...
FROM table_name
WHERE column GLOB pattern;

The table below describes patterns which is used with GLOB operator and uses (*) and (?).

PatternDescription
'J*'A value that start with "J".
'*n'A value that end with "n".
'*oh*'A value that have "oh" in any position.
'?o*'A value that have "o" in the second position.
'J?*'A value that start with "J" and have at least 2 characters.
'J??*'A value that start with "J" and have at least 3 characters.
'J*n'A value that start with "J" and ends with "n".

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch all records from Employee table where Salary starts with 3 and ends with 0, the query is:

    SELECT * FROM Employee
    WHERE Salary GLOB '3*0';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    4KimAmsterdam303100
    5RameshNew Delhi283000
  • Similarly, to fetch all records from Employee table where Name of the employee starts with 'Jo', the query is:

    SELECT * FROM Employee
    WHERE Name GLOB 'Jo*';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
  • Using NOT GLOB operator: NOT GLOB operator is used as the negation of GLOB operator. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:

    SELECT * FROM Employee
    WHERE Name NOT GLOB 'Jo*';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800

❮ SQLite - Operators