SQLite GLOB Operator

  Total Views : 31
  Comments : 0
 
  Keywords : SQLite glob clause with example, SQLite glob query in select statement example, SQLite glob() function with example, How to use sqlite glob to check string matches with pattern example

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 example.

SQLite GLOB Operator

In SQLite GLOB operator is used to check whether the given string value matching with specific pattern or not. In case if string value matches with pattern value then it will return true and it’s similar to 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 select statement.

 

SELECT FROM table_name

WHERE column_name GLOB 'search-expression';

In above SQLite GLOB syntax, we defined GLOB operator with 'search expression' in 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 result.

SQLite GLOB Operator Examples

We will see how to use SQLite GLOB operator with example for that create emp_master table and insert some data in it using 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 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 wild character ? in 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 example for that write 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

Following table give 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 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User