SQLite Glob clause

SQLite's GLOB operator is used to match text values in patterns specified by wildcards. If the search expression matches the pattern expression, the GLOB operator returns true, which is 1. Unlike the LIKE operator, GLOB is case-sensitive and follows UNIX syntax for the following wildcard characters.

Asterisk (*)
Question mark (?)

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

Syntax

* The basic syntax of 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 use the AND or OR operator to combine N quantity conditions. Here, XXXX can be any number or string value.

Examples

The following examples demonstrate the differences in GLOB clauses with '*' and '?' operators:

##WHERE SALARY GLOB '200*'Find any value starting with 200WHERE SALARY GLOB '*200*'Find any value containing 200 at any positionWHERE SALARY GLOB '?00*'Find any value whose second and third digits are 00WHERE SALARY GLOB '2??'Find any value that starts with 2 and has a length of at least Any value of 3 charactersWHERE SALARY GLOB '*2'Find any value ending with 2## WHERE SALARY GLOB '?2*3'WHERE SALARY GLOB '2???3 '

Let's give us a practical example. ----- ---------- ---------- ----------

1 32 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 1000000.0

The following is an example, which displays all records in the COMPANY table whose AGE starts with 2:

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

This will produce the following results:

ID ------ ---------- ----------
2 Texas ‐ 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 the ADDRESS text in the COMPANY table All records containing a hyphen (-):

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

This will produce the following results:

ID ------- ----------

4 but

← SQLite Like clause

SQLite Limit clause →

submit