位置:首頁 > 數據庫 > SQLite基礎教程 > SQLite GLOB子句

SQLite GLOB子句

SQLite的GLOB運算符用於對圖案使用通配符隻匹配文本值。如果搜索表達式可以匹配的圖案表達,GLOB運算將返回true,也就是1。 LIKE操作符不同,GLOB是大小寫敏感的,它遵循以下通配符指定的UNIX語法。

  • 星號 (*)

  • 問號(?)

星號表示零個或多個數字或字符。?代表一個單一的數字或字符。

語法

*和?基本語法如下:

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 '????'

可以結合的條件下使用N多的AND或OR運算。這裡XXXX可能是任何數字小或字符串值。

例子:

這裡有數量的例子顯示WHERE部分具有不同的LIKE子句有'*'和'?'運算符:

語句 描述
WHERE SALARY GLOB '200*' Finds any values that start with 200
WHERE SALARY GLOB '*200*' Finds any values that have 200 in any position
WHERE SALARY GLOB '?00*' Finds any values that have 00 in the second and third positions
WHERE SALARY GLOB '2??' Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY GLOB '*2' Finds any values that end with 2
WHERE SALARY GLOB '?2*3' Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY GLOB '2???3' Finds any values in a five-digit number that start with 2 and end with 3

讓我們舉一個實際的例子,考慮公司表有以下記錄:

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

以下是一個例子,它會顯示從COMPANY 表年齡以2開始的所有記錄:

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

這將產生以下結果:

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

以下是一個例子,它會顯示所有記錄從COMPANY 表的ADDRESS將有一個連字符( - ),裡麵的文字:

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

這將產生以下結果:

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