❮ Sqlite Perl Sqlite Index ❯

SQLite `Glob Clause`

The GLOB operator in SQLite is used to match text values against a pattern specified by wildcard characters. If the search expression matches the pattern expression, the GLOB operator returns true (1). Unlike the LIKE operator, GLOB is case-sensitive and follows the UNIX syntax for the following wildcards:

Asterisk (*)

Question Mark (?)

The asterisk (*) represents zero, one, or multiple numbers or characters. The question mark (?) represents a single number or character. These symbols can be used in combination.

Syntax

The basic syntax for * and ? 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 combine conditions using the AND or OR operators. Here, XXXX can be any numeric or string value.

Examples

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

Statement	Description
WHERE SALARY GLOB '200*'	Finds any values that start with 200
WHERE SALARY GLOB '200'	Finds any values that have 200 in any position
WHERE SALARY GLOB '?00*'	Finds any values that have 00 in the second and third positions
WHERE SALARY GLOB '2??'	Finds any values that start with 2 and are at least 3 characters long
WHERE SALARY GLOB '*2'	Finds any values that end with 2
WHERE SALARY GLOB '?2*3'	Finds any values that have 2 in the second position and end with 3
WHERE SALARY GLOB '2???3'	Finds any values that are 5 characters long, start with 2, and end with 3

Let's take a practical example. Suppose 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

Here is an example that shows all records from 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 example shows 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

❮ Sqlite Perl Sqlite Index ❯

SQLite Glob Clause

Syntax

Examples

SQLite `Glob Clause`