Updated on 2024-09-19 GMT+08:00

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, delimeter,[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, delimeter, 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.
  • delimeter01: delimiter 1.
  • delimeter02: 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.

    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"}