SQLite GLOB Operator

Here we will learn sqlite glob clause or operator with example and how to use sqlite glob to match text values against a pattern with an example.

SQLite GLOB Operator

In SQLite GLOB operator is used to check whether the given string value matches a specific pattern or not. In case if string value matches with pattern value then it will return true and it’s similar to a LIKE operator.

 

In SQLite GLOB is a case sensitive, so 'a' GLOB 'A' is false and all non-NULL parameter expressions will be converted to text values. 

Syntax of SQLite GLOB Operator

Following is the Syntax of SQLite GLOB operator with a select statement.

 

SELECT FROM table_name

WHERE column_name GLOB 'search-expression';

In the above SQLite GLOB syntax, we defined the GLOB operator with 'search expression' in a select statement.

 

In SQLite with GLOB operator, we need to use some wild card character which some special meaning. They are as follows:

 

  • The * character in the pattern will match zero or more characters in the search expression.
  • The [] will match exactly one character from its set of characters.
  • The ^ used within [] will negotiate results.

SQLite GLOB Operator Examples

We will see how to use SQLite GLOB operator with the example for that create emp_master table and insert some data in it using the following queries.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3),

(9,'Shwets','Jariwala', 19400,2);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

SQLite GLOB with * Character

Following is the example of using SQLite GLOB operator with wild character * in a select statement.

 

sqlite> SELECT * FROM emp_master WHERE salary GLOB '1*';

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

9           Shwets      Jariwala    19400       2

The above SQLite GLOB example will give the employees whose salary starts with 1 means 10000, 10100, 19300, etc.

SQLite GLOB with ? Character

Now we will see how to use SQLite GLOB operator with a wild character ? in the select statement. Write query like as shown following to use SQLite GLOB operator.

 

sqlite> SELECT * FROM emp_master WHERE first_name GLOB 'Shwet?';

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

5           Shweta      Rana        12000       3

9           Shwets      Jariwala    19400       2

The above SQLite GLOB example will give the employees whose first name having 6 characters and starts with ‘Shwet’.

SQLite GLOB with [] Character

Now we will see how to use SQLite GLOB operator with wild characters [] with the example for that write a query like as shown following.

 

sqlite> SELECT * FROM emp_master WHERE last_name GLOB '[A-J]*';

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

9           Shwets      Jariwala    19400       2

The above SQLite GLOB example will give the employees whose last name starts with A to J.

SQLite GLOB with ^ Character

Now we will see how to use SQLite GLOB with ^ character to negotiate the result for that we need to write the code like as shown below.

 

sqlite> SELECT * FROM emp_master WHERE last_name GLOB '[^A-J]*';

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

The above SQLite GLOB example will give the employees whose last name not starts with A to J.

SQLite GLOB Operator with Wildcard Characters

The following table gives more info about using GLOB operator with wildcard characters * and ?.

 

ExpressionsDescription
Where name GLOB '*t' It returns all the users whose name ends with ‘t’
Where name GLOB 't*' It returns all the users whose name starts with ‘t’
Where name GLOB '*tut*' It returns all the users whose name contains ‘tut’
Where name GLOB '?t' It returns all the users whose name contains ‘t’ at the second position
Where name GLOB 't??*e' It returns all the users whose name starts with ‘t’ and ends with ‘e’ and name should contain at least 4 characters

 This is how we can use SQLite GLOB operator to get matched expression values based on our requirements.