SQLite Glob Clause

SQLite'sGLOBThe operator is used to match text values against patterns specified by wildcards. If the search expression matches the pattern expression, the GLOB operator will return true (true), i.e. 1. Unlike the LIKE operator, GLOB is case-sensitive, and for the following wildcards it follows UNIX syntax.

  • *: Matches zero, one or more digits or characters.
  • ?: Represents a single digit or character.
  • [...]: Matches one of the characters specified in the brackets. For example,[abc]Matches any one of the characters "a", "b", or "c".
  • [^...]: Matches one of the characters not specified in the brackets. For example,[^abc]Matches a character that is not any of "a", "b", or "c".

The above symbols can be used in combination.

Syntax

*and?The basic syntax is as follows:

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 '?XXXX'

or

SELECT FROM table_name
WHERE column GLOB '?XXXX?'

or

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

You can use the AND or OR operators to combine N numbers of conditions. Here, XXXX can be any numeric or string value.

Examples

The following examples demonstrate the differences of the GLOB clause with the '*' and '?' operators:

StatementDescription
WHERE SALARY GLOB '200*'Find any value that starts with 200
WHERE SALARY GLOB '*200*'Find any value that contains 200 at any position
WHERE SALARY GLOB '?00*'Find any value with 00 in the second and third positions
WHERE SALARY GLOB '2??'Find any value that starts with 2 and is 3 characters in length; for example, it may match values such as "200", "2A1", "2B2".
WHERE SALARY GLOB '*2'Find any value that ends with 2
WHERE SALARY GLOB '?2*3'Find any value with 2 in the second position and ending with 3
WHERE SALARY GLOB '2???3'Find any value that is 5 digits long, starts with 2 and ends with 3

Let us take a practical example. Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following is an example that displays all records in the COMPANY table where AGE starts with 2:

sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following is an example that displays all records in the COMPANY table where the ADDRESS text contains a hyphen (-):

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

[...] Wildcard

[...]The expression is used to match any one character from the character set specified in the brackets.

Example 1: Match product names that start with "A" or "B".

SELECT * FROM products WHERE product_name LIKE '[AB]%';

This will match product names that start with "A" or "B".

Example 2: Match phone numbers that start with "1", "2", or "3".

SELECT * FROM customers WHERE phone_number LIKE '[123]%';

This will match phone numbers that start with "1", "2", or "3".

[^...] Wildcard

[^...]The expression is used to match any character that is not in the character set specified in the brackets.

Example 1: Match product codes that do not start with "X" or "Y".

SELECT * FROM products WHERE product_code LIKE '[^XY]%';

This will match products that do not start with "X" or "Y"

codes.

Example 2: Match usernames that do not contain numeric characters.

SELECT * FROM users WHERE username LIKE '[^0-9]%';

This will match usernames that do not start with numeric characters.

Other Extensions