split_by_string

description

Syntax

  1. split_by_string(s, separator)

Splits a string into substrings separated by a string. It uses a constant string separator of multiple characters as the separator. If the string separator is empty, it will split the string s into an array of single characters.

Arguments

separator — The separator. Type: String

s — The string to split. Type: String

Returned value(s)

Returns an array of selected substrings. Empty substrings may be selected when:

A non-empty separator occurs at the beginning or end of the string;

There are multiple consecutive separators;

The original string s is empty.

Type: Array(String)

notice

Only supported in vectorized engine

example

  1. SELECT split_by_string('1, 2 3, 4,5, abcde', ', ');
  2. select split_by_string('a1b1c1d','1');
  3. +---------------------------------+
  4. | split_by_string('a1b1c1d', '1') |
  5. +---------------------------------+
  6. | ['a', 'b', 'c', 'd']            |
  7. +---------------------------------+
  9. select split_by_string(',,a,b,c,',',');
  10. +----------------------------------+
  11. | split_by_string(',,a,b,c,', ',') |
  12. +----------------------------------+
  13. | ['', '', 'a', 'b', 'c', '']      |
  14. +----------------------------------+
  16. SELECT split_by_string(NULL,',');
  17. +----------------------------+
  18. | split_by_string(NULL, ',') |
  19. +----------------------------+
  20. | NULL                       |
  21. +----------------------------+
  23. select split_by_string('a,b,c,abcde',',');
  24. +-------------------------------------+
  25. | split_by_string('a,b,c,abcde', ',') |
  26. +-------------------------------------+
  27. | ['a', 'b', 'c', 'abcde']            |
  28. +-------------------------------------+
  30. select split_by_string('1,,2,3,,4,5,,abcde', ',,');
  31. +---------------------------------------------+
  32. | split_by_string('1,,2,3,,4,5,,abcde', ',,') |
  33. +---------------------------------------------+
  34. | ['1', '2,3', '4,5', 'abcde']                |
  35. +---------------------------------------------+
  37. select split_by_string(',,,,',',,');
  38. +-------------------------------+
  39. | split_by_string(',,,,', ',,') |
  40. +-------------------------------+
  41. | ['', '', '']                  |
  42. +-------------------------------+
  44. select split_by_string(',,a,,b,,c,,',',,');
  45. +--------------------------------------+
  46. | split_by_string(',,a,,b,,c,,', ',,') |
  47. +--------------------------------------+
  48. | ['', 'a', 'b', 'c', '']              |
  49. +--------------------------------------+

keywords

SPLIT_BY_STRING,SPLIT