SQLite 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:

snippet
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'

Syntax for question mark:

snippet
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:

Sqlite Glob clause 1

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:

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

Output:

Sqlite Glob clause 2

Example2:

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

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

Output:

Sqlite Glob clause 3
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents