In this blog we will learn about SQLite GLOB operator. GLOB operator is used for matching string values against patterns. GLOB operator works like ‘like’ operator in database query, it also used wildcards for matching string values within patterns, if the expression can be matched to the pattern expression, the GLOB operator return true, which is equal to ‘1’. The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards, the following wildcards are as follows:
- (*) asterisk sign : The asterisk sign represents zero or multiple numbers or characters.
- (?) question mark : The question mark represents a single number or character.
Syntax of (*):
SELECT FROM table_name WHERE column_name GLOB 'XXXX*'
or
SELECT FROM table_name WHERE column_name GLOB '*XXXX*'
Syntax of (?):
SELECT FROM table_name WHERE column_name GLOB 'XXXX?'
or
SELECT FROM table_name WHERE column_name GLOB '?XXXX'
or
SELECT FROM table_name WHERE column_name GLOB '?XXXX?'
or
SELECT FROM table_name WHERE column_name GLOB '????'
We can use any number of conditions using AND or OR operators, here
XXXX could be any String value.
Example:
Some examples showing WHERE part having different GLOB clause with '*'
and '?' Operators:
Statement | Description |
WHERE CONTACT GLOB '222*' | Finds any values that start with 222 |
WHERE CONTACT GLOB '*222*' | Finds any values that have 222 in any position |
WHERE CONTACT GLOB '?22*' | Finds any values that have 22 in the second and third positions |
WHERE CONTACT GLOB '2??' | Finds any values that start with 2 and are at least 3 characters in length |
WHERE CONTACT GLOB '*2' | Finds any values that end with 2 |
WHERE CONTACT GLOB '?2*3' | Finds any values that have a 2 in the second position and end with a 3 |
WHERE CONTACT GLOB '2???3' | Finds any values in a five-digit number that start with 2 and end with 3 |
Now, Let us take a real example, consider EMPLOYEE table is having the following records:
ID | NAME | AGE | ADDRESS | SALARY |
1 | AJAY | 32 | CalCUTTA | 1111111111 |
2 | VIJAY | 25 | DELHI-EAST | 2222222222 |
3 | VINAY | 23 | VARANSI | 3333333333 |
4 | SUBHASH | 25 | PUNJAB | 4444444444 |
5 | SURESH | 27 | DELHI-WEST | 5555555555 |
6 | PREETI | 22 | RAEBARELI | 6666666666 |
7 | PRABHAT | 24 | DELHI-NORTH | 7777777777 |
8 | REENA | 44 | MUMBAI | 8888888888 |
9 | PINKI | 45 | DELHI-SOUTH | 9999999999 |
Following is an example, which will display all the records from EMPLOYEE
table where AGE starts with 2:
sqlite> SELECT * FROM EMPLOYEE WHERE AGE GLOB '2*';
Here is the result after execution:
ID | NAME | AGE | ADDRESS | SALARY |
2 | VIJAY | 25 | DELHI-EAST | 2222222222 |
3 | VINAY | 23 | VARANSI | 3333333333 |
4 | SUBHASH | 25 | PUNJAB | 4444444444 |
5 | SURESH | 27 | DELHI-WEST | 5555555555 |
6 | PREETI | 22 | RAEBARELI | 6666666666 |
7 | PRABHAT | 24 | DELHI-NORTH | 7777777777 |
Another example, which will display all the records from EMPLOYEE table
where ADDRESS will have a hyphen (-) inside the text:
sqlite> SELECT * FROM EMPLOYEE WHERE ADDRESS GLOB '*-*';
Here is the result after execution:
ID | NAME | AGE | ADDRESS | SALARY |
2 | VIJAY | 25 | DELHI-EAST | 2222222222 |
5 | SURESH | 27 | DELHI-WEST | 5555555555 |
7 | PRABHAT | 24 | DELHI-NORTH | 7777777777 |
9 | PINKI | 45 | DELHI-SOUTH | 9999999999 |
Leave Comment