Example of GLOB Operator
To understand the GLOB Operator in SQLite we need a table on which we will perform various operation and queries. So here we have students table which consist of stu_id, first_name, fees, and email as Columns. If you don’t know How To Create a Table in SQLite then refer this.
After inserting some data into the students table our table looks like:
Example 1: Glob Operator Starting With Specified Pattern
In this examples, We are going to use the Wildcard at the back of the specified pattern. Basically we are searching for the pattern that is going to start with the specified pattern.
Syntax:
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
Query:
Let’s Retrieve all student records from the students
table whose first names begin with the letter “R”.
SELECT * from students
WHERE first_name GLOB 'R*';
Output:
Explanation: In the above Query We have fetch everything from the students table where the first character of first name of every student is R.
Example 2: Glob Operator In Between Pattern
Here we are going to search for the pattern that is inbetween the text. We are going to add wildcards infront and at the back and then we are going to find out the pattern based on the specified pattern.
Syntax:
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
Query:
Let’s Find and retrieve all information for faculty members whose names contain the letter “a” between the name from the faculty
table.
SELECT * FROM faculty
WHERE name GLOB '*a*';
Output:
Explanation: In the above Query, We have seen that “*a*” which means we are asking for the first_name that contains the letter “a” between the names in the students table.
Example 3: Glob Operator Starting And Ending Pattern
In this example we are going to search for the pattern that is going to starts and end. Here we are going to specify the starting and ending of the pattern.
Syntax:
SELECT FROM table_name
WHERE column GLOB 'X???X'
Explanation of Syntax: Select statement to fetch the data followed by the table name and WHERE clause followed by the columname and here comes the GLOB clause with the pattern that is going to find in starting and ending of the pattern.
Query:
Let’s Find and retrieve all student information for those with fees starting with 3, ending with 0, and having exactly three characters in between, as recorded in the students
table.
SELECT * FROM students
WHERE fees GLOB '3???0';
Output:
Explanation: In the above Query, We have fetch the everything from the students table for fees columns in which 3 should be at first position and 0 at the last positions and also there should be 3 character between them i.e. 3???0.
Example 4: Glob Operator Positions
In this below example we are going to search for the pattern that is going to be in the certain positions. Here we are going to use the wildcards wherever needed.
Syntax:
SELECT FROM table_name
WHERE column GLOB '?XXXX*'
Explanation of Syntax: Select statement to fetch the data followed by the table name and WHERE clause followed by the columname and here comes the GLOB clause with the pattern that is going to fetch the data by using the certain position of the pattern.
Query:
Let’s retrieve full details for all students in the students
table whose recorded fees include the pattern “00” at any position in the fees.
SELECT * FROM students
WHERE fees GLOB '?00*';
Output:
Explanation: In the above Query, We have fetched everything from the students table based on the condition. It means we are asking for the fees that starts with the any digit and ends with the any digit but it should contain 0′s in the second and thirds positions and hence whole table is retrieved as it matches the pattern.
SQLite GLOB Clause
SQLite is a server-less database engine and it is written in C programming language. The main moto for developing SQLite is to escape from using complex database engines like MYSQL etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, web browsers, and many more.
It is written simply so that it can be embedded into other applications. As we know we have a large database, so to find the specific string it is the best operator to search for the specific pattern.
In the article, We will be learning about how the SQLite GLOB Operator works, and along with that we will be also learning about how the SQLite GLOB function works with the examples.