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.
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.
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:
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
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.
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’.
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.
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.
The following table gives more info about using GLOB operator with wildcard characters * and ?.
Expressions | Description |
---|---|
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.