SQLite Glob Clause

“SQLite is an in-process module that provides an interactional SQL database system that is self-contained, virtualized, and requires no initialization. In the SQLite database, the LIKE clause and the GLOB clause work similarly.  The GLOB clause is used to see if a character satisfies a pattern or not. The GLOB clause, unlike the LIKE clause, is a specific instance and utilizes UNIX special characters. Furthermore, the GLOB layouts lack some escape symbols.

The special characters that are being used in the GLOB clause would be as follows:

• The asterisk (*) symbol accommodates every set of characters.
• The symbol of the question mark (?) fits only one element.

We will also utilize the listing wildcard [] to identify a single element from a list of items in addition to these special characters. A set of letters can also be used with the listing wildcard. Furthermore, the symbol ^ at the start of any list will be used to identify any element excluding that specified item within the list.

We’ll go over the details of the SQLite glob and illustrate how to employ it to retrieve information from the table in this article.

For running the queries, we have to install any compiler. Here we installed the software “BD Browser for SQLite.” First of all, we have created a new database by selecting the “New database” option from the menu bar. This would be saved into the folder having the type SQLite database files. To create a new Database, we can also execute the query. Then we will create a table by running its specific query.”

Create a Table

We are going to create a table named “Professor” and store the data in it. This table has different columns, including “Id,” “Name,” “Gender,” “Age,” “City,” and “Salary.” ID has an “integer” data type. Whereas Name, Gender, and the City have “text” data type. The attribute Age and Salary has a NUMERIC data type. The primary key of this table is “ID.”

The output shows that the CREATE query is successfully executed.

Insert the Data

After creating the table “student,” we now insert the data of the student in this table. So we have utilized the “INSERT” query.

We effectively inserted the data into the columns of table “Professor.”

SELECT Query

Now we want to run the query of “SELECT” to fetch the entire data of the table “Professor.” We can get the entire data from the table or retrieves the specific data from the table by mentioning the names of columns in the “SELECT” query.

This query of SELECT retrieves the data of all the columns of the table “Professor.”

Use GLOB Clause

The GLOB clause in SQLite is being used to determine if a required value matches a particular set. It’s identical to a LIKE operator in that it provides true when any value matches the template parameter.

In this instance, we have used the GLOB clause in the WHERE command of the SELECT query. We have applied the GLOB clause to the column “Id” of the table.

The query returns the record of those professors whose value of Id starts from “30”. The resultant table has only one professor who fulfills this condition.

Here we will apply the GLOB clause in such a way that the SELECT query retrieves the data of professors whose Id has 94 in the second and third positions.

The SELECT query is successfully executed, as shown in the figure.

We will obtain a record of those professors whose age starts with 4 and has at least 1 element in length. For this purpose, the GLOB clause can be used.

In the output, we get the data of only four professors whose age begins with 4 and has 1 more element.

In this case, we have utilized the GLOB clause on the Name column of the table. We will find the record of that professor whose Name ends with the alphabets “ia.”

The outcome of the above query is shown below:

We are going to retrieve only the Id, Name, and City of those professors whose value of City has “a” in the second position and terminate with the “I” alphabet.

The SELECT query fetches the data of some specific columns which are given in the above query.

Here we don’t want to acquire the entire data of the table “Professor.” We just need data for three columns, so we will specify the names of those columns in the SELECT query. The GLOB clause is applied to the “Name” column.

After running the above query, we get the Name, Gender, and Age of those professors whose name has five alphabets in it and the name starting with the alphabet “A” and ending with the alphabet “n.” We have only one professor who satisfies the GLOB condition.

In this example, we want to fetch the data from the table “Professor,” where the Gender of the professors contains “ema” at any position.

In the result, we have records of only three professors whose value of Gender contains “ema” in it.

We are going to employ the GLOB clause on the column “Name” in such a way that the query will return the records of only those professors whose name begins with A to J.

The SELECT query only provides the Id, Name, and City of those professors who fulfills the GLOB condition.

Conclusion

In this article, we have discussed how to use the GLOB clause in SQLite in detail. The main query of SQLite DB is the SELECT query. We can specify what to retrieve in the SELECT section. The FROM statement of the query would be applied to designate where data should be fetched from. We can get the data from the table by specifying different conditions in the GLOB clause of the SELECT query.