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:
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.06 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:
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 '*-*';
ID ------- ----------
4 butStatement | Description |
---|---|
Find any value whose second digit is 2 and ends with 3 | |
Find any value that is 5 digits long and starts with 2 and ends with 3 |