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 split. The data type is STRING.

• delimiter: Required. The separator that is used to split the string. This parameter supports regular expressions. The data type is STRING.

• trimTailEmpty: Optional. Specifies whether to keep trailing empty strings. The default value is true. If you set this parameter to false, trailing empty strings are kept. The data type is BOOLEAN.

Return value

Returns a value of the ARRAY<STRING> type.

Examples

• Example 1: Split a string using a comma.

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

• Example 2: Handle cases in which the separator does not exist.

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

• Example 3: Handle consecutive separators. This creates an empty string element.

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

• Example 4: Use a multi-character separator.

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

• Example 5: Keep trailing empty strings.

  -- By default, trailing empty strings are not returned.
  -- Returns ["a","b","c"].
  SELECT SPLIT('a,b,c,,', ',');
  
  -- Return trailing empty strings.
  -- Returns ["a","b","c","",""].
  SELECT SPLIT('a,b,c,,', ',', false);

• Example 6: Use an escape character or a special character as the separator.

  -- Split by a line feed.
  -- 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.
  -- Returns ["line1","line2"].
  SELECT SPLIT('line1\rline2', '\r');  
  
  -- Escape a backslash.
  -- Returns ["a","b","c"].
  SELECT SPLIT('a\\b\\c', '\\\\');  

• Example 7: Handle NULL inputs.

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

Related functions

The SPLIT function is a string function. For more functions that you can use to search for strings and transform string formats, see String functions.