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

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.

  1. Example field

     Id: dc1dab7e-b045-4e77-bda4-914d083d1bf7

  2. Query and analysis statement

     SELECT split(Id,'-'), split(Id,'-',2)
  3. 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.

  1. Example field

     Id: dc1dab7e-b045-4e77-bda4-914d083d1bf7

  2. Query and analysis statement

     SELECT split_part(Id,'-',1)
  3. 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.

  1. Example field

     Request: request_id:"e3ac4b70c7d244f080d434e300d8065a"; request_time: "1674965051000"

  2. Query and analysis statement

     SELECT split_to_map(Request,';',':')
  3. Query and analysis result

     Table 3 Query and analysis result of the split_to_map function

     EXPR$0
     {"request_id ":"e3ac4b70c7d244f080d434e300d8065a ","request_time":"1674965051000"}