String functions supported by Lindorm SQL - Lindorm - Alibaba Cloud Documentation Center

This topic describes the string functions supported by Lindorm SQL and provides examples on how to use the functions.

Applicable engines and versions

String functions described in this topic are applicable only to LindormTable 2.5.1.1 and later versions.

Note

For more information about how to view and upgrade the LindormTable version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Supported string functions

The following table lists the string functions supported by Lindorm SQL.

Function	Description
CONCAT	Concatenates multiple strings into a new string.
LENGTH	Calculates the length of a string.
LOWER	Converts all letters in a string to lowercase letters.
MD5	Calculates the MD5 hash of a string.
SHA256	Calculates the SHA256 hash of a string.
REPLACE	Replaces a substring that matches the specified rule with a new substring.
REVERSE	Reverses a string.
REGEXP_REPLACE	Replaces a substring that matches the specified rule with a new substring from the specified position in a string.
REGEXP_SUBSTR	Returns a substring that starts from the specified position in a string and matches the specified rule.
SUBSTR	Returns a substring of the specified length.
START_WITH	Determines whether the prefix of a string matches the specified string.
TRIM	Removes the leading and trailing spaces of a string.
UPPER	Converts all letters in a string to uppercase letters.
MATCH	Checks whether the values of the specified columns match the specified rule.

CONCAT

The CONCAT function concatenates multiple strings into a new string.

Syntax

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

Parameters

Parameter	Required	Description
'string1','string2',...,'stringN'	Yes	The strings that you want to concatenate.

Examples

Concatenate the a, b, and c strings into a string abc.

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

The following result is returned:

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

The concatenated string that contain no delimiters is returned.

LENGTH

The LENGTH function calculates the length of a string.

Syntax

LENGTH('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.

Examples

Calculate the length of the string abc.

SELECT length('abc') AS len;

The following result is returned:

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

The returned value 3 indicates that the length of the string abc is 3 characters.

LOWER

The LOWER function converts all letters in a string to lowercase letters.

Syntax

LOWER('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to convert.

Examples

Example 1: Convert all letters in the string ABC to lowercase letters.
```
SELECT lower('ABC') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
The string abc is returned. All letters in the original string are converted to lowercase letters.
Example 2: Convert all letters in the string Abc to lowercase letters.
```
SELECT lower('Abc') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
The string abc is returned. All uppercase letters in the original string are converted to lowercase letters.

MD5

The MD5 function calculates the MD5 hash of a string.

Syntax

MD5('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.

Examples

Calculate the MD5 hash of the string abc.

SELECT md5('abc') AS val;

The following result is returned:

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

The MD5 hash of the string abc is returned.

SHA256

The SHA256 function calculates the SHA256 hash of a string.

Syntax

SHA256('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.

Examples

In this example, a sample table is created and populated by executing the following statements:

-- Create a sample table.
CREATE TABLE tb (id int, name varchar, address varchar, PRIMARY KEY(id, name)); 

-- Insert data into the created table.
UPSERT INTO tb (id, name, address) VALUES (1, 'jack',  'hz');

Query the SHA256 hash of the value in the name column of the row whose id is 1.

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

The following result is returned:

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

The SHA256 hash of the string jack is returned.

REPLACE

The REPLACE function replaces a substring that matches the specified rule with a new substring.

Syntax

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

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.
from_str	Yes	The substring that you want to replace.
to_str	Yes	The substring with which you want to replace the substring that matches the rule.

Examples

Example 1: Replace all substrings that match the substring bc in the string abc with the substring cd.
```
SELECT replace('abc','bc','cd') AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
| acd |
+-----+
```
The string acd is returned. The substring bc in the string abc is replaced with the substring cd.
Example 2: Replace all substrings that match the substring bc in the string abcbc with the substring cd.
```
SELECT replace('abcbc', 'bc', 'cd') AS val;
```
The following result is returned:
```
+-------+
|  val  |
+-------+
| acdcd |
+-------+
```
The string acdcd is returned. All substrings bc in the string abcbc are replaced with the substring cd.

REVERSE

The REVERSE function reverses a string.

Syntax

REVERSE('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.

Examples

Reverse the string abc.

SELECT reverse('abc') AS val;

The following result is returned:

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

The string cba is returned. This string is reversed from the string abc.

REGEXP_REPLACE

The REGEXP_REPLACE function replaces a substring that matches the specified rule with a new substring from the specified position in a string.

Syntax

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

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.
pattern	Yes	The regular expression that is used to specify the match rule.
rep	Yes	The substring with which you want to replace the substring that matches the rule.
position	No	The position from which the substrings to be replaced are checked based on the rule. The value of this parameter is an integer greater than or equal to 1. If you do not specify this parameter, the function checks the substrings that start from the first character of the string based on the rule.

Examples

Example 1: Do not specify the pos parameter. In this case, the function checks the substrings that start from the first character of the string and replaces the substring that matches the substring b with the substring c.
```
SELECT regexp_replace('abc', 'b', 'c') AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
| acc |
+-----+
```
The string acc is returned. The substring that matches the substring b is replaced with the substring c.
Example 2: Set the pos parameter to 2. In this case, the function checks the substrings that start from the second character of the string and replaces the substring that matches the substring b with the substring c.
```
SELECT regexp_replace('abcbc', 'b', 'c', 2) AS val;
```
The following result is returned:
```
+-------+
|  val  |
+-------+
| acccc |
+-------+
```

The string acccc is returned. The function checks the substrings that start from the second character and match the substring b in the string abcbc, and then replaces the substrings with the substring c.

Example 3: Set the pos parameter to 3. In this case, the function checks the substrings that start from the third character of the string and replaces the substring that matches the substring b with the substring c.
```
SELECT regexp_replace('abcbc', 'b', 'c', 3) AS val;
```
The following result is returned:
```
+-------+
|  val  |
+-------+
| abccc |
+-------+
```
The string abccc is returned. The function checks the substrings that start from the third character and match the substring b in the string abcbc, and then replaces the substrings with the substring c.

REGEXP_SUBSTR

The REGEXP_SUBSTR function returns a substring that starts from the specified position in a string and matches the specified rule.

Syntax

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

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.
pattern	Yes	The regular expression that is used to specify the match rule.
position	No	The position from which the substrings to be replaced are checked based on the rule. The value of this parameter is an integer greater than or equal to 1. If you do not specify this parameter, the function checks the substrings that start from the first character of the string based on the rule.

Examples

Example 1: Set the pos parameter to 3. In this case, the function checks whether substrings that start from the third character of the string abc match the substring b specified by the rule.
```
 SELECT regexp_substr('abc', 'b', 3) AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
|     |
+-----+
```
No value is returned, which indicates no substrings that start from the third character of the string abc match the substring b.
Example 2: Do not specify the pos parameter. In this case, the function checks whether substrings that start from the first character of the string abc match the substring b specified by the rule.
```
SELECT regexp_substr('abc', 'b') AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
| b   |
+-----+
```
The substring b is returned. This substring is truncated after the first character of the string abc and matches the rule.

SUBSTR

The SUBSTR function returns a substring of the specified length.

Syntax

SUBSTR( string, pos, [len])

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.
position	Yes	The position from which the substring is truncated. The value of this parameter is an integer greater than or equal to 1.
len	No	The length of the substring that you want to truncate. The value of this parameter is an integer greater than or equal to 1. If you do not specify this parameter, the substring from the position specified by `pos` to the end of the string is returned by default.

Examples

Example 1: Do not specify the len parameter. In this case, the substring from the second character to the end of the string abc is returned by default.
```
SELECT substr('abc', 2) AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
| bc  |
+-----+
```
The substring bc is returned. This substring is truncated from the second character to the end of the string abc.
Example 2: Specify the len parameter. In this case, the substring from the first character to the second character of the string abc is returned.
```
SELECT substr('abc', 1, 2) AS val;
```
The following result is returned:
```
+-----+
| val |
+-----+
| ab  |
+-----+
```
The substring ab is returned. This substring is truncated from the first character to the second character of the string abc.

START_WITH

The START_WITH function determines whether the prefix of a string matches the specified string.

Syntax

START_WITH('string1', 'string2')

Parameters

Parameter	Required	Description
string1	Yes	The string that you want to process.
string2	Yes	The string that you use to match the prefix.

Examples

Example 1: Determine whether the string ab is a prefix of the string abc.
```
SELECT start_with('abc', 'ab') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| true   |
+--------+
```
The returned value is true, which indicates that the string ab is a prefix of the string abc.
Example 2: Determine whether the string bc is a prefix of the string abc.
```
SELECT start_with('abc', 'bc') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| false  |
+--------+
```
The returned value is false, which indicates that the string bc is not the prefix of the string abc.

TRIM

The TRIM function removes the leading and trailing spaces of a string.

Syntax

TRIM('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to process.

Examples

Remove the leading and trailing spaces of the string abc.

SELECT trim(' abc    ') AS str;

The following result is returned:

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

The string with the leading and trailing spaces removed is returned.

UPPER

The UPPER function converts all letters in a string to uppercase letters.

Syntax

UPPER('string')

Parameters

Parameter	Required	Description
string	Yes	The string that you want to convert.

Examples

Example 1: Convert all letters in the string abc to uppercase letters.
```
SELECT upper('abc') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
The string ABC is returned. All letters in the original string are converted to uppercase letters.
Example 2: Convert all letters in the string aBC to uppercase letters.
```
SELECT upper('aBC') AS val;
```
The following result is returned:
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
The string ABC is returned. All lowercase letters in the original string are converted to uppercase letters.

MATCH

The MATCH function determines whether the values of the specified columns match the specified rule.

Important

Only LindormTable 2.7.2 and later versions support the MATCH function. For information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Syntax

MATCH (column_identifiers) AGAINST (search_expr [search_modifier])

Note

You can use the MATCH function only in the WHERE clause for an SQL query.
By default, the return data of a query statement that contains the MATCH function is sorted in reverse order based on the proximity of the column values to the specified rule.

Parameters

Parameter	Required	Description
column_identifiers	Yes	The columns that you want to match against the specified rule. You can enter multiple columns. Separate the columns with commas (,). If you enter multiple columns, the columns are combined and then matched against the specified rule. Important You must create search indexes for the columns specified by the column_identifiers parameter. The analyzers are configured for the columns for word segmentation. For more information, see Enable the search index feature and CREATE INDEX.
search_expr	Yes	A string constant that is used as the match rule. For more information, see the Match rule description section of this topic.
search_modifier	No	The search_modifier parameter can be set to the `IN BOOLEAN MODE` clause. The query results are the same as the results when you do not add the IN BOOLEAN MODE clause. Example: `SELECT * FROM tb WHERE MATCH (c1) AGAINST ('+hello' IN BOOLEAN MODE);`.

Match rule description

A match rule consists of one or more conditions. Separate the conditions with spaces. A condition can be one of the following items:

A single word. Data that contains the specified word matches this condition. Example: hello.
A phrase enclosed in double quotation marks (""). Data that contains an entire phrase without word segmentation matches this condition. For example, "hello world" indicates that the data that contains the "hello world" phrase matches the rule.
Another match rule enclosed in parentheses (()). Data that matches a match rule enclosed in parentheses matches this condition. Example: (another "hello world").

You can change the match behavior of a condition by adding a symbol before the condition:

+ indicates that the condition must be met.
- indicates that the condition cannot be met.
If no symbol is added before a condition, the condition is not mandatory. However, data that meets the condition can get a higher ranking.

Examples

In the following examples, a sample table is created and populated by executing the following statements:

-- Create a sample table named tb.
CREATE TABLE tb (id INT, c1 VARCHAR, PRIMARY KEY(id)); 

-- Create a search index. Before you create search indexes, make sure that the search index feature is enabled.
CREATE INDEX idx USING SEARCH ON tb (c1(type=text));

-- Insert data into the table.
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');

Example 1: Query data that contains the word hello or world in the c1 column.

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

The following result is returned:

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

Example 2: Query data that may contain the word hello and must contain the word world in the c1 column.

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

The following result is returned:

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

Example 3: Query data that contains the word world but does not contain the word hello in the c1 column.

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

The following result is returned:

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

Example 4: Query data that contains the phrase hello world in the c1 column.

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

The following result is returned:

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

Example 5: Query data that must contain the word hello and at least one word from the words you and me in the c1 column.

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

The following result is returned:

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