Lindorm SQL支援的字串函數 - Lindorm

本文介紹Lindorm支援的字串函數的用法及樣本。

引擎與版本

字串函數適僅用於寬表引擎，且引擎版本需為2.5.1.1及以上版本。

說明

函數列表

Lindorm支援的字串函數如下表所示：

函數	說明
CONCAT	將多個字串拼接成一個新的字串。
LENGTH	計算字串的長度。
LOWER	將字串中所有的字母都轉換為小寫字母。
MD5	計算指定字串的MD5值。
SHA256	計算指定字串的SHA256編碼值。
REPLACE	將匹配指定規則的子串替換為新的字串。
REVERSE	返回逆序的字串。
REGEXP_REPLACE	從字串指定位置開始，將匹配指定規則的子串替換為新的字串。
REGEXP_SUBSTR	返回從字串指定位置開始，匹配指定規則的子串。
SUBSTR	返回字串中指定長度的子串。
START_WITH	判斷字串的首碼是否為指定字串。
TRIM	刪除字串前後的空格。
UPPER	將字串中所有的字母都轉換為大寫字母。
MATCH	判斷指定列的值是否匹配指定的規則。

CONCAT函數

將多個字串拼接成一個新的字串。

文法

CONCAT('string1','string2',...,'stringN')

參數說明

參數	是否必填	說明
'string1','string2',...,'stringN'	是	需要拼接的字串，多個字串之間用英文逗號（,）分隔。

樣本

將單獨的字串a、b和c進行拼接，組成一個新的字串abc。

SELECT concat('a','b','c') AS val;

返回結果：

+--------+
| val    |
+--------+
| abc    |
+--------+

返回結果為拼接後的新字串，且字串中間無間隔符。

LENGTH函數

計算字串的長度。

文法

LENGTH('string')

參數說明

參數	是否必填	說明
string	是	待匹配的字串。

樣本

計算字串abc的長度。

SELECT length('abc') AS len;

返回結果：

+-----+
| len |
+-----+
| 3   |
+-----+

返回結果為3，表示字串abc的長度為3。

LOWER函數

將字串中所有的字母都轉換為小寫字母。

文法

LOWER('string')

參數說明

參數	是否必填	說明
string	是	待轉換的字串。

樣本

樣本1：將字串ABC中所有的字母都轉換為小寫字母。
```
SELECT lower('ABC') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
返回結果為abc，表示已將字串ABC中所有的大寫字母都轉換為小寫字母abc。
樣本2：將字串Abc中所有的字母都轉換為小寫字母。
```
SELECT lower('Abc') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
返回結果為abc，表示已將字串Abc中所有的大寫字母都轉換為小寫字母abc。

MD5函數

計算指定字串的MD5值。

文法

MD5('string')

參數說明

參數	是否必填	說明
string	是	待匹配的字串。

樣本

計算指定字串abc的MD5值。

SELECT md5('abc') AS val;

返回結果：

+----------------------------------+
|               val                |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+

返回結果為字串abc的MD5值。

SHA256函數

計算指定字串的SHA256編碼值。

文法

SHA256('string')

參數說明

參數	是否必填	說明
string	是	待編碼的字串。

樣本

假設表結構及表中資料如下：

-- 建立樣本表
CREATE TABLE tb (id int, name varchar, address varchar, PRIMARY KEY(id, name)); 

-- 寫入資料
UPSERT INTO tb (id, name, address) VALUES (1, 'jack',  'hz');

查詢id值為1的行中對應的name列資料的SHA256編碼值。

SELECT sha256(name) AS sc FROM tb WHERE id=1;

返回結果：

+------------------------------------------------------------------+
|                                sc                                |
+------------------------------------------------------------------+
| 31611159e7e6ff7843ea4627745e89225fc866621cfcfdbd40871af4413747cc |
+------------------------------------------------------------------+

返回結果為字串jack的SHA256編碼值。

REPLACE函數

將匹配指定規則的子串替換為新的字串。

文法

REPLACE('string','from_str','to_str')

參數說明

參數	是否必填	說明
string	是	待匹配的字串。
from_str	是	指定字串。
to_str	是	替換的字串。

樣本

樣本1：將字串abc中所有匹配bc的子串都替換為cd。
```
SELECT replace('abc','bc','cd') AS val;
```
返回結果：
```
+-----+
| val |
+-----+
| acd |
+-----+
```
返回結果為acd，表示已將字串abc中匹配bc的子串替換為cd。
樣本2：將字串abcbc中所有匹配bc的子串都替換為cd。
```
SELECT replace('abcbc', 'bc', 'cd') AS val;
```
返回結果：
```
+-------+
|  val  |
+-------+
| acdcd |
+-------+
```
返回結果為acdcd，表示已將字串abcbc中匹配bc的子串替換為cd。

REVERSE函數

返回逆序的字串。

文法

REVERSE('string')

參數說明

參數	是否必填	說明
string	是	待匹配的字串。

樣本

將字串abc逆序輸出。

SELECT reverse('abc') AS val;

返回結果：

+-----+
| val |
+-----+
| cba |
+-----+

返回結果為cba ，表示已將字串abc逆序輸出。

REGEXP_REPLACE函數

從字串指定位置開始，將匹配指定規則的子串替換為新的字串。

文法

REGEXP_REPLACE('string',pat,rep,[pos])

參數說明

參數	是否必填	說明
string	是	待匹配的字串。
pattern	是	Regex。
rep	是	替換的字串。
position	否	開始匹配字串的位置，取值為大於等於1的整數。不指定該參數時，預設從第1個字元開始匹配。

樣本

樣本1：不指定pos參數。預設從第1個字元開始匹配，並將匹配b的子串替換為c。
```
SELECT regexp_replace('abc', 'b', 'c') AS val;
```
返回結果：
```
+-----+
| val |
+-----+
| acc |
+-----+
```
返回結果為acc，表示已將字串abc中匹配b的子串替換為c。
樣本2：指定pos參數。從第2個字元開始匹配，並將匹配b的子串替換為c。
```
SELECT regexp_replace('abcbc', 'b', 'c', 2) AS val;
```
返回結果：
```
+-------+
|  val  |
+-------+
| acccc |
+-------+
```

acccc，表示已將字串abcbc中第2個字元至字串末尾匹配b的子串替換為c。

樣本3：指定pos參數。從第3個字元開始匹配，並將匹配b的子串替換為c。
```
SELECT regexp_replace('abcbc', 'b', 'c', 3) AS val;
```
返回結果：
```
+-------+
|  val  |
+-------+
| abccc |
+-------+
```
返回結果為abccc，表示已將字串abcbc中第3個字元至字串末尾匹配b的子串替換為c。

REGEXP_SUBSTR函數

返回從字串指定位置開始，匹配指定規則的子串。

文法

REGEXP_SUBSTR('string', pat, [pos])

參數說明

參數	是否必填	說明
string	是	待匹配的字串。
pattern	是	Regex。
position	否	開始匹配字串的位置，取值為大於等於1的整數。不指定該參數時，預設從第1個字元開始匹配。

樣本

樣本1：指定pos參數，從字串abc的第3個字元開始匹配b的子串。
```
 SELECT regexp_substr('abc', 'b', 3) AS val;
```
返回結果：
```
+-----+
| val |
+-----+
|     |
+-----+
```
返回結果為空，表示從字串第3個字元開始無匹配b的子串。
樣本2：不指定pos參數，預設從字串abc的第1個字元開始匹配b的子串。
```
SELECT regexp_substr('abc', 'b') AS val;
```
返回結果：
```
+-----+
| val |
+-----+
| b   |
+-----+
```
返回結果為b，表示從字串第1個字元開始截取匹配b的子串。

SUBSTR函數

返回字串中指定長度的子串。

文法

SUBSTR( string, pos, [len])

參數說明

參數	是否必填	說明
string	是	待匹配的字串。
position	是	開始截取字串的位置，取值為大於等於1的整數。
len	否	從左向右截取字串的長度，取值為大於等於1的整數。不指定該參數時，則預設返回`pos`參數指定位置到字串末尾的子串。

樣本

樣本1：不指定len參數，預設返回字串abc第2個字元到字串末尾的子串。
```
SELECT substr('abc', 2) AS val;
```
返回結果：
```
+-----+
| val |
+-----+
| bc  |
+-----+
```
返回結果為bc，表示字串abc第2個字元至字串末尾的子串。
樣本2：指定len參數，返回字串abc第1個字元到第2個字元的子串。
```
SELECT substr('abc', 1, 2) AS val;
```
返回結果：
```
+-----+
| val |
+-----+
| ab  |
+-----+
```
返回結果為ab，表示字串abc第1個字元到第2個字元的子串。

START_WITH函數

判斷字串的首碼是否為指定字串。

文法

START_WITH('string1', 'string2')

參數說明

參數	是否必填	說明
string1	是	待匹配的字串。
string2	是	指定字串。

樣本

樣本1：判斷字串abc的首碼是否為指定字串ab。
```
SELECT start_with('abc', 'ab') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| true   |
+--------+
```
返回結果為true，表示字串abc的首碼是指定字串ab。
樣本2：判斷字串abc的首碼是否為指定字串bc。
```
SELECT start_with('abc', 'bc') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| false  |
+--------+
```
返回結果為false，表示字串abc的首碼不是指定字串bc。

TRIM函數

刪除字串前後的空格。

文法

TRIM('string')

參數說明

參數	是否必填	說明
string	是	待匹配的字串。

樣本

刪除指定字串 abc 前後的空格。

SELECT trim(' abc    ') AS str;

返回結果：

+-----+
| str |
+-----+
| abc |
+-----+

返回結果為刪除前後空格後的結果。

UPPER函數

將字串中所有的字母都轉換為大寫字母。

文法

UPPER('string')

參數說明

參數	是否必填	說明
string	是	待轉換的字串。

樣本

樣本1：將字串abc中所有的字母都轉換為大寫字母。
```
SELECT upper('abc') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
返回結果為ABC，表示已將字串abc中所有的小寫字母轉換為大寫字母ABC。
樣本2：將字串aBC中所有的字母都轉換為大寫字母。
```
SELECT upper('aBC') AS val;
```
返回結果：
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
返回結果為ABC，表示已將字串aBC中所有的小寫字母都轉換為大寫字母ABC。

MATCH函數

判斷指定列的值是否匹配指定的規則。

重要

僅寬表引擎2.7.2及以上版本支援MATCH函數。您可以通過升級小版本，將目前的版本升級至2.7.2及以上。
僅搜尋索引支援使用MATCH函數。當查詢包含MATCH函數、且搜尋索引存在時，會直接選中搜尋索引。

注意事項

在使用MATCH函數查詢資料前，您需要注意以下效能相關事項及索引相關要求：

查詢條件中包含非索引列時，系統會先通過搜尋索引對索引列條件執行查詢，再在結果集中對非索引列進行過濾（逐條檢查非索引列的條件是否滿足）。由於資料查詢的效能與索引條件命中數量有關，因此當過濾資料量過大時會導致查詢效率降低。您可以根據業務的查詢模式，通過ADD COLUMN（新增列）將需要查詢的列添加至搜尋索引。
目前主表及二級索引不支援MATCH函數。在使用MATCH函數查詢資料時，系統會直接命中搜尋索引。如果希望查詢命中主表或二級索引，在滿足檢索需求的前提下可以在查詢語句中添加LIKE關鍵詞將分詞查詢修改為模糊查詢。
重要
模糊查詢雖然可以命中主表和二級索引，但查詢效能比分詞查詢差。
如需同時使用MATCH函數和LIKE關鍵詞查詢資料，要求對應的列必須以分詞子欄位的方式添加至搜尋索引。

文法

MATCH (column_identifiers) AGAINST (search_expr)

說明

目前MATCH函數只能用於SQL查詢的WHERE子句中。
包含MATCH函數的查詢語句，其結果預設按照MATCH函數的匹配程度倒序排序。

參數說明

參數	是否必填	說明
column_identifiers	是	需要進行匹配的列。如有多個列，請以半形逗號（,）分隔。提供了多個列時，這些列的內容將會組合後共同參與匹配。重要 column_identifiers指定的列必須已建立搜尋索引，且均為分詞列。搜尋索引的開通及建立方式，請參見開通搜尋索引和CREATE INDEX。
search_expr	是	一個字串常量，指定匹配規則字串。詳細說明，請參見匹配規則說明。

匹配規則說明

匹配規則由一個或多個條件構成，每個條件之間以空格分隔。一個條件可以是以下幾種之一：

單個詞語，表示期望包含該詞語，例如hello。
以雙引號包圍的短語，表示期望包含這個短語的整體，不做分詞，例如"hello world"表示擷取包含"hello world"短語的資料。
以括弧包圍的另一個匹配規則，表示期望滿足括弧內的匹配規則，例如(another "hello world")。

在條件前添加符號，可以改變該條件的匹配行為：

+表示該條件必須被滿足。
-表示該條件不能被滿足。
不帶符號時，表示該條件不必須被滿足，但滿足了該條件的資料可以獲得更靠前的排序。

樣本

假設表結構及表中資料如下：

-- 建立樣本表tb
CREATE TABLE tb (id INT, c1 VARCHAR, PRIMARY KEY(id)); 

-- 建立搜尋索引。建立前請確保已開通搜尋索引
CREATE INDEX idx USING SEARCH ON tb (c1(type=text));

-- 向樣本表tb中插入樣本資料
UPSERT INTO tb (id,c1) VALUES (1,'hello');
UPSERT INTO tb (id,c1) VALUES (2,'world');
UPSERT INTO tb (id,c1) VALUES (3,'hello world');
UPSERT INTO tb (id,c1) VALUES (4,'hello my world');
UPSERT INTO tb (id,c1) VALUES (5,'hello you');
UPSERT INTO tb (id,c1) VALUES (6,'hello you and me');
UPSERT INTO tb (id,c1) VALUES (7,'you and me');

樣本一：查詢c1列中包含單詞hello或單詞world的資料。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('hello world');

返回結果：

+----+------------------+
| id |        c1        |
+----+------------------+
| 3  | hello world      |
| 2  | world            |
| 4  | hello my world   |
| 5  | hello you        |
| 1  | hello            |
| 6  | hello you and me |
+----+------------------+

樣本二：查詢c1列中可能包含單詞hello，但必須包含單詞world的資料。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('hello +world');

返回結果：

+----+----------------+
| id |       c1       |
+----+----------------+
| 3  | hello world    |
| 2  | world          |
| 4  | hello my world |
+----+----------------+

樣本三：查詢c1列中包含單詞world，但不包含單詞hello的資料。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('-hello +world');

返回結果：

+----+-------+
| id |  c1   |
+----+-------+
| 2  | world |
+----+-------+

樣本四：查詢c1列中包含短語hello world的資料。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('"hello world"');

返回結果：

+----+-------------+
| id |     c1      |
+----+-------------+
| 3  | hello world |
+----+-------------+

樣本五：查詢c1列中包含單詞hello，且同時包含單詞you或單詞me的資料。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('+hello +(you me)');

返回結果：

+----+------------------+
| id |        c1        |
+----+------------------+
| 6  | hello you and me |
| 5  | hello you        |
+----+------------------+