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.

GLOB Operator in SQLite

It is used to match only the text values against a pattern using wildcards. Here wildcard is nothing but the special character ( " * ", " "), and if the search expression is matched to the pattern expression then it returns true.

SQLite GLOB operator is case-sensitive. In simple words GLOB operator checks for the value of the specific string that is going to match the specific pattern or not.

• The asterisk sign (*)
  
• The question mark (?)

The asterisk sign represents many numbers or characters whereas the question mark represents only a single number or a character.

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.

Conclusion

SQLite GLOB operator is similar to LIKE but however Glob operator uses the UNIX file and it is CASE SENSITIVE. Using this function we can find our desired output very easily by writing the pattern in the huge databases. Glob operator is used to match the text values from the pattern that we specify.

We can specify the patterns as such that should be able to give the resulting ouput. The asterisk(*) wild card is used when we need to find multiple characters or numbers or zero. The question mark(?) wild card is used when we need to search for the single pattern or character. By the end of the article you will get knowledge on how the SQLite Glob operator works and the functionality of it.