Latest web development tutorials

SQLite Glob clause

SQLite TheGLOB operator is used to match a text value specified wildcard pattern.If the search expression and expression pattern matching, GLOB operator returns true (true), which is 1. And the LIKE operator is different, GLOB is case sensitive, for the following wildcards, which follows the UNIX syntax.

  • Asterisk(*)

  • question mark(?)

An asterisk (*) represents zero, one or more digits or characters. The question mark (?) Represents a single digit or character. These symbols can be used in combination.

grammar

? * And the basic syntax is as follows:

SELECT FROM table_name
WHERE column GLOB 'XXXX *'

or 

SELECT FROM table_name
WHERE column GLOB '* XXXX *'

or

SELECT FROM table_name
WHERE column GLOB 'XXXX?'

or

SELECT FROM table_name
WHERE column GLOB '? XXXX'

or

SELECT FROM table_name
WHERE column GLOB '? XXXX?'

or

SELECT FROM table_name
WHERE column GLOB '????'

You can use the AND or OR operator to combine the N number of conditions. Here, XXXX can be any number or string value.

Examples

The following examples demonstrate the different places GLOB clause with '*' and operators '?':

语句描述
WHERE SALARY GLOB '200*'查找以 200 开头的任意值
WHERE SALARY GLOB '*200*'查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??'查找以 2 开头,且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2'查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3'查找第二位为 2,且以 3 结尾的任意值
WHERE SALARY GLOB '2???3'查找长度为 5 位数,且以 2 开头以 3 结尾的任意值

Let's take a practical example, suppose COMPANY table has the following records:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Here is an example, it shows the COMPANY table AGE 2 all records that begin with:

sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2 *';

This produces the following results:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Here is an example, it displays the text COMPANY ADDRESS table contains a hyphen (-) all records:

sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '* - *';

This produces the following results:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0