MaxCompute:SPLIT

Syntax

ARRAY<STRING> SPLIT(STRING <source>, STRING <delimiter>[, BOOLEAN <trimTailEmpty>])

-- Standard example.
-- Returns ["a","b","c"].
SELECT SPLIT('a,b,c', ',');

Parameters

• source: Required. The STRING to be split.

• delimiter: Required. The separator pattern. Can be a plain string or a regular expressions.

• trimTailEmpty: Optional. A BOOLEAN that specifies how to handle trailing empty strings.

  • true(Default): Removes trailing empty strings.

  • false: Retains trailing empty strings.

Return value

Returns a value of type ARRAY<STRING>.

Examples

• Example 1: Split by a comma.

  -- Returns ["a","b","c"].
  SELECT SPLIT('a,b,c', ',');

• Example 2: Delimiter not found.

  -- Returns ["a,b,c"].
  SELECT SPLIT('a,b,c', ':');

• Example 3: Consecutive delimiters.

  -- Returns ["a","","b"].
  SELECT SPLIT('a,,b', ',');

• Example 4: Multi-character delimiter.

  -- Returns ["a","b","c"].
  SELECT SPLIT('a::b::c', '::');

• Example 5: Controlling trailing empty strings.

  -- The trimTailEmpty parameter defaults to true, so trailing empty strings are discarded.
  -- Returns ["a","b","c"].
  SELECT SPLIT('a,b,c,,', ',');
  
  -- Set the parameter to false to retain trailing empty strings.
  -- Returns ["a","b","c","",""].
  SELECT SPLIT('a,b,c,,', ',', false);

• Example 6: Special character delimiters.

  -- Split by a newline character.
  -- Returns ["hello","world"].
  SELECT SPLIT('hello\nworld', '\n');  
  
  -- Split by a tab character.
  -- Returns ["a","b","c"].
  SELECT SPLIT('a\tb\tc', '\t');  
  
  -- Split by a carriage return character.
  -- Returns ["line1","line2"].
  SELECT SPLIT('line1\rline2', '\r');  
  
  -- Split by a backslash character.
  -- Returns ["a","b","c"].
  SELECT SPLIT('a\\b\\c', '\\\\');

• Example 7: NULL handling.

  -- If any parameter is NULL, the result is NULL.
  -- Returns NULL.
  SELECT SPLIT(NULL, ',');
  
  -- Returns NULL.
  SELECT SPLIT('a,b,c', NULL);
  
  -- Returns NULL.
  SELECT SPLIT('a,b,c', ',', NULL);

Related functions

SPLIT is categorized under string functions. For more functions that search and format strings, see String functions.