SQL SPLIT Functions

Description

SPLIT functions split a character string using a delimiter, and return the substrings.

Syntax

SELECT split_to_map(x, delimiter01, delimiter02)

SPLIT Function Statements

Statement	Description	Example	Parameter
split	Splits a character string using a delimiter and returns the substrings.	split(x, delimiter,[limit])	x: varchar parameter value. delimiter: delimiter. limit: an integer greater than 0, specifying the maximum number into which the character string can be split.
split_part	Splits a character string using a specified delimiter and returns the indexed content.	split_part(x, delimiter, part)	x: varchar parameter value. delimiter: delimiter. part: index of the field to return.
split_to_map	Splits a character string using the first delimiter, and then splits the string again using the other delimiter.	split_to_map(x, delimiter01, delimiter02)	x: varchar parameter value. delimiter01: delimiter 1. delimiter02: delimiter 2.

Examples

split

Splits a target character string according to a specified string. limit specifies the maximum number of words to split into. If this parameter is not specified, all words are split by default.

Example field
Id: dc1dab7e-b045-4e77-bda4-914d083d1bf7
Query and analysis statement
```
SELECT split(Id,'-'), split(Id,'-',2)
```

Query and analysis result

**Table 1** Query and analysis result of the split function
EXPR$0	EXPR$1
["dc1dab7e","b045","4e77","bda4","914d083d1bf7"]	["dc1dab7e","b045-4e77-bda4-914d083d1bf7"]

split_part

Splits a character string and returns the indexed content. The index starts from 0. If it is negative or greater than the number of substrings, an empty string is returned.

Example field
Id: dc1dab7e-b045-4e77-bda4-914d083d1bf7
Query and analysis statement
```
SELECT split_part(Id,'-',1)
```

Query and analysis result

**Table 2** Query and analysis result of the split_part function
EXPR$0
b045

split_to_map

Splits a character string using the first delimiter, and then splits the string again using the other delimiter. The result is displayed in format {"KEY1":"VALUE1","KEY2":"VALUE2"}. If a value cannot be split again, it is left blank.

Example field
Request: request_id:"e3ac4b70c7d244f080d434e300d8065a"; request_time: "1674965051000"
Query and analysis statement
```
SELECT split_to_map(Request,';',':')
```

Query and analysis result