Reference for all supported SQL string functions - Lindorm

Lindorm SQL supports a set of string functions for manipulating, searching, and hashing string values. All functions described on this page require LindormTable 2.5.1.1 or later.

To check your current version or upgrade, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Supported string functions

Category	Function	Description
General manipulation	CONCAT	Concatenates multiple strings into a single string
	LENGTH	Returns the number of characters in a string
	REPLACE	Replaces all occurrences of a substring
	REVERSE	Reverses a string
	SUBSTR	Extracts a substring by position and length
	TRIM	Removes leading and trailing spaces
Case conversion	LOWER	Converts all letters to lowercase
	UPPER	Converts all letters to uppercase
Regular expressions	REGEXP_REPLACE	Replaces substrings matching a regular expression
	REGEXP_SUBSTR	Extracts the first substring matching a regular expression
Prefix matching	START_WITH	Returns true if a string starts with a given prefix
Full-text search	MATCH	Matches column values against a search expression using a search index
Cryptographic hashes	MD5	Returns the MD5 hash of a string
	SHA256	Returns the SHA256 hash of a string

CONCAT

Concatenates two or more strings into a single string. No delimiter is added between values.

Syntax

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

Parameters

Parameter	Required	Description
`'string1', 'string2', ..., 'stringN'`	Yes	The strings to concatenate. Pass two or more string values.

Example

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

Result:

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

LENGTH

Returns the number of characters in a string.

Syntax

LENGTH('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to measure.

Example

SELECT length('abc') AS len;

Result:

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

LOWER

Converts all letters in a string to lowercase. Non-letter characters are not affected.

Syntax

LOWER('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to convert.

Examples

Example 1: Convert ABC to lowercase.

SELECT lower('ABC') AS val;

Result:

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

Example 2: Convert a mixed-case string to lowercase.

SELECT lower('Abc') AS val;

Result:

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

UPPER

Converts all letters in a string to uppercase. Non-letter characters are not affected.

Syntax

UPPER('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to convert.

Examples

Example 1: Convert abc to uppercase.

SELECT upper('abc') AS val;

Result:

+-----+
| val |
+-----+
| ABC |
+-----+

Example 2: Convert a mixed-case string to uppercase.

SELECT upper('aBC') AS val;

Result:

+-----+
| val |
+-----+
| ABC |
+-----+

TRIM

Removes leading and trailing spaces from a string. Spaces within the string are preserved.

Syntax

TRIM('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to trim.

Example

SELECT trim(' abc    ') AS str;

Result:

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

REPLACE

Replaces all occurrences of a substring within a string.

Syntax

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

Parameters

Parameter	Required	Description
`string`	Yes	The source string.
`from_str`	Yes	The substring to find and replace.
`to_str`	Yes	The replacement substring.

Examples

Example 1: Replace bc in abc with cd.

SELECT replace('abc', 'bc', 'cd') AS val;

Result:

+-----+
| val |
+-----+
| acd |
+-----+

Example 2: Replace all occurrences of bc in abcbc with cd.

SELECT replace('abcbc', 'bc', 'cd') AS val;

Result:

+-------+
|  val  |
+-------+
| acdcd |
+-------+

REVERSE

Reverses the characters in a string.

Syntax

REVERSE('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to reverse.

Example

SELECT reverse('abc') AS val;

Result:

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

SUBSTR

Extracts a substring starting at a given position, with an optional length limit.

Syntax

SUBSTR(string, position [, length])

Parameters

Parameter	Required	Description
`string`	Yes	The source string.
`position`	Yes	The 1-based position at which extraction starts. Must be an integer >= 1.
`length`	No	The number of characters to extract. Must be an integer >= 1. Default: extracts from `position` to the end of the string.

Examples

Example 1: Extract from position 2 to the end of the string.

SELECT substr('abc', 2) AS val;

Result:

+-----+
| val |
+-----+
| bc  |
+-----+

Example 2: Extract 2 characters starting at position 1.

SELECT substr('abc', 1, 2) AS val;

Result:

+-----+
| val |
+-----+
| ab  |
+-----+

START_WITH

Returns true if a string starts with the specified prefix, and false otherwise.

Syntax

START_WITH('string', 'prefix')

Parameters

Parameter	Required	Description
`string`	Yes	The string to check.
`prefix`	Yes	The prefix to match against the beginning of `string`.

Examples

Example 1: Check whether abc starts with ab.

SELECT start_with('abc', 'ab') AS val;

Result:

+------+
| val  |
+------+
| true |
+------+

Example 2: Check whether abc starts with bc.

SELECT start_with('abc', 'bc') AS val;

Result:

+-------+
| val   |
+-------+
| false |
+-------+

REGEXP_REPLACE

Replaces substrings that match a regular expression, starting the search from a specified position.

Syntax

REGEXP_REPLACE('string', pattern, replacement [, position])

Parameters

Parameter	Required	Description
`string`	Yes	The source string.
`pattern`	Yes	The regular expression pattern defining the match rule.
`replacement`	Yes	The string to substitute for each match.
`position`	No	The 1-based character position at which to start searching. Must be an integer >= 1. Default: `1` (starts from the first character).

Examples

Example 1: Replace all matches of b in abc with c (default position).

SELECT regexp_replace('abc', 'b', 'c') AS val;

Result:

+-----+
| val |
+-----+
| acc |
+-----+

Example 2: Replace matches of b in abcbc starting from position 2.

SELECT regexp_replace('abcbc', 'b', 'c', 2) AS val;

Result:

+-------+
|  val  |
+-------+
| acccc |
+-------+

Example 3: Replace matches of b in abcbc starting from position 3. The b at position 2 is not replaced.

SELECT regexp_replace('abcbc', 'b', 'c', 3) AS val;

Result:

+-------+
|  val  |
+-------+
| abccc |
+-------+

REGEXP_SUBSTR

Returns the first substring that matches a regular expression, starting the search from a specified position.

Syntax

REGEXP_SUBSTR('string', pattern [, position])

Parameters

Parameter	Required	Description
`string`	Yes	The source string.
`pattern`	Yes	The regular expression pattern defining the match rule.
`position`	No	The 1-based character position at which to start searching. Must be an integer >= 1. Default: `1` (starts from the first character).

Examples

Example 1: Search for b in abc from the first character (default).

SELECT regexp_substr('abc', 'b') AS val;

Result:

+-----+
| val |
+-----+
| b   |
+-----+

Example 2: Search for b in abc starting from position 3. No match is found because b is at position 2.

SELECT regexp_substr('abc', 'b', 3) AS val;

Result:

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

MD5

Returns the MD5 hash of a string.

Syntax

MD5('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to hash.

Example

SELECT md5('abc') AS val;

Result:

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

SHA256

Returns the SHA256 hash of a string.

Syntax

SHA256('string')

Parameters

Parameter	Required	Description
`string`	Yes	The string to hash.

Example

This example creates a sample table, inserts a row, then queries the SHA256 hash of a column value.

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

-- Insert a row.
UPSERT INTO tb (id, name, address) VALUES (1, 'jack', 'hz');

-- Query the SHA256 hash of the name column.
SELECT sha256(name) AS sc FROM tb WHERE id = 1;

Result:

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

MATCH

Searches column values using a full-text search expression and a search index. Results are sorted by relevance in descending order by default.

Important

MATCH requires LindormTable 2.7.2 or later. To upgrade, see Release notes of LindormTable and upgrade your instance.
MATCH works only with search indexes. When both a MATCH condition and a search index exist, the system uses the search index automatically.

Usage notes

Non-indexed columns: The system first retrieves rows via the search index, then filters non-indexed columns row by row. This can degrade performance on large datasets. To avoid this, use the ADD COLUMN statement to add the relevant columns to the search index.
Primary tables and secondary indexes: MATCH is not supported on primary tables or secondary indexes. For those, use LIKE for fuzzy queries instead. Fuzzy queries deliver lower performance than tokenized queries.
Combining MATCH and LIKE: When using both in the same query, configure the relevant columns as tokenized columns in the search index.

Syntax

MATCH (column_identifiers) AGAINST (search_expr)

MATCH can only be used in the WHERE clause of a SELECT statement.

Parameters

Parameter	Required	Description
`column_identifiers`	Yes	One or more column names to search, separated by commas. If multiple columns are specified, their values are combined and matched together. Search indexes must exist for all specified columns, with analyzers configured for word segmentation. See Enable the search index feature and CREATE INDEX.
`search_expr`	Yes	A string constant defining the match rule. See Match rule syntax below.

Match rule syntax

A match rule is one or more space-separated conditions. Each condition is one of:

A single word — matches rows containing that word. Example: hello
A quoted phrase — matches rows containing the exact phrase without word segmentation. Example: "hello world"
A parenthesized sub-rule — matches rows satisfying the enclosed rule. Example: (another "hello world")

Prefix a condition with a symbol to change its behavior:

Symbol	Meaning
`+`	The condition must be met (AND)
`-`	The condition must not be met (NOT)
(none)	The condition is optional, but matching rows rank higher

Examples

The following examples use this sample table:

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

-- Create a search index. Enable the search index feature before running this statement.
CREATE INDEX idx USING SEARCH ON tb (c1(type=text));

-- Insert rows.
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: Return rows where c1 contains hello or world (or both). Rows matching both terms rank higher.

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

Result:

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

Example 2: Return rows where c1 must contain world and optionally contains hello. Rows with both terms rank higher.

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

Result:

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

Example 3: Return rows where c1 contains world but not hello.

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

Result:

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

Example 4: Return rows where c1 contains the exact phrase hello world.

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

Result:

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

Example 5: Return rows where c1 must contain hello and at least one of you or me.

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

Result:

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