blog

Home / DeveloperSection / Blogs / Glob operator of SQLite Database

Glob operator of SQLite Database

Tarun Kumar3451 21-Sep-2015

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

 


Updated 13-Mar-2018

Leave Comment

Comments

Liked By