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	是	正则表达式。
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	是	正则表达式。
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函数。如何查看或升级当前版本，请参见宽表引擎版本说明和升级小版本。

语法

MATCH (column_identifiers) AGAINST (search_expr [search_modifier])

说明

目前MATCH函数只能用于SQL查询的WHERE子句中。
包含MATCH函数的查询语句，其结果默认按照MATCH函数的匹配程度倒序排序。

参数说明

参数	是否必填	说明
column_identifiers	是	需要进行匹配的列。如有多个列，请以半角逗号（,）分隔。提供了多个列时，这些列的内容将会组合后共同参与匹配。重要 column_identifiers指定的列必须已创建搜索索引，且均为分词列。搜索索引的开通及创建方式，请参见开通搜索索引和CREATE INDEX。
search_expr	是	一个字符串常量，指定匹配规则字符串。详细说明，请参见匹配规则说明。
search_modifier	否	search_modifier可以指定为`IN BOOLEAN MODE`，查询效果与不添加该子句时相同。例如：`SELECT * FROM tb WHERE MATCH (c1) AGAINST ('+hello' IN BOOLEAN MODE);`。

匹配规则说明

匹配规则由一个或多个条件构成，每个条件之间以空格分隔。一个条件可以是以下几种之一：

单个词语，表示期望包含该词语，例如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        |
+----+------------------+