Array functions·续

Example

> select array_replace([1, 2, 2, 3, 2, 1, 4], 2, 5);
+--------------------------------------------------------+
| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+--------------------------------------------------------+
| [1, 5, 2, 3, 2, 1, 4]                                  |
+--------------------------------------------------------+

Aliases

  • list_replace

array_replace_n

Replaces the first max occurrences of the specified element with another specified element.

array_replace_n(array, from, to, max)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • from: Initial element.
  • to: Final element.
  • max: Number of first occurrences to replace.

Example

> select array_replace_n([1, 2, 2, 3, 2, 1, 4], 2, 5, 2);
+-------------------------------------------------------------------+
| array_replace_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(5),Int64(2)) |
+-------------------------------------------------------------------+
| [1, 5, 5, 3, 2, 1, 4]                                             |
+-------------------------------------------------------------------+

Aliases

  • list_replace_n

array_replace_all

Replaces all occurrences of the specified element with another specified element.

array_replace_all(array, from, to)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • from: Initial element.
  • to: Final element.

Example

> select array_replace_all([1, 2, 2, 3, 2, 1, 4], 2, 5);
+------------------------------------------------------------+
| array_replace_all(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+------------------------------------------------------------+
| [1, 5, 5, 3, 5, 1, 4]                                      |
+------------------------------------------------------------+

Aliases

  • list_replace_all

array_reverse

Returns the array with the order of the elements reversed.

array_reverse(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_reverse([1, 2, 3, 4]);
+------------------------------------------------------------+
| array_reverse(List([1, 2, 3, 4]))                          |
+------------------------------------------------------------+
| [4, 3, 2, 1]                                               |
+------------------------------------------------------------+

Aliases

  • list_reverse

array_slice

Returns a slice of the array based on 1-indexed start and end positions.

array_slice(array, begin, end)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • begin: Index of the first element. If negative, it counts backward from the end of the array.
  • end: Index of the last element. If negative, it counts backward from the end of the array.
  • stride: Stride of the array slice. The default is 1.

Example

> select array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6);
+--------------------------------------------------------+
| array_slice(List([1,2,3,4,5,6,7,8]),Int64(3),Int64(6)) |
+--------------------------------------------------------+
| [3, 4, 5, 6]                                           |
+--------------------------------------------------------+

Aliases

  • list_slice

array_to_string

Converts each element to its text representation.

array_to_string(array, delimiter)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • delimiter: Array element separator.

Example

> select array_to_string([[1, 2, 3, 4], [5, 6, 7, 8]], ',');
+----------------------------------------------------+
| array_to_string(List([1,2,3,4,5,6,7,8]),Utf8(",")) |
+----------------------------------------------------+
| 1,2,3,4,5,6,7,8                                    |
+----------------------------------------------------+

Aliases

  • array_join
  • list_join
  • list_to_string

array_union

Returns an array of elements that are present in both arrays (all elements from both arrays) with out duplicates.

array_union(array1, array2)

Arguments

  • array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_union([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 3, 4]);           |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                                 |
+----------------------------------------------------+
> select array_union([1, 2, 3, 4], [5, 6, 7, 8]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 7, 8]);           |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8]                           |
+----------------------------------------------------+

Aliases

  • list_union

array_except

Returns an array of the elements that appear in the first array but not in the second.

array_except(array1, array2)

Arguments

  • array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_except([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [5, 6, 3, 4]);           |
+----------------------------------------------------+
| [1, 2]                                 |
+----------------------------------------------------+
> select array_except([1, 2, 3, 4], [3, 4, 5, 6]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [3, 4, 5, 6]);           |
+----------------------------------------------------+
| [1, 2]                                 |
+----------------------------------------------------+

Aliases

  • list_except

cardinality

Returns the total number of elements in the array.

cardinality(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select cardinality([[1, 2, 3, 4], [5, 6, 7, 8]]);
+--------------------------------------+
| cardinality(List([1,2,3,4,5,6,7,8])) |
+--------------------------------------+
| 8                                    |
+--------------------------------------+

empty

Returns 1 for an empty array or 0 for a non-empty array.

empty(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select empty([1]);
+------------------+
| empty(List([1])) |
+------------------+
| 0                |
+------------------+

Aliases

  • array_empty,
  • list_empty

generate_series

Similar to the range function, but it includes the upper bound.

generate_series(start, stop, step)

Arguments

  • start: start of the range
  • end: end of the range (included)
  • step: increase by step (can not be 0)

Example

> select generate_series(1,3);
+------------------------------------+
| generate_series(Int64(1),Int64(3)) |
+------------------------------------+
| [1, 2, 3]                          |
+------------------------------------+

list_append

Alias of array_append.

list_cat

Alias of array_concat.

list_concat

Alias of array_concat.

list_dims

Alias of array_dims.

list_distinct

Alias of array_dims.

list_element

Alias of array_element.

list_empty

Alias of empty.

list_except

Alias of array_element.

list_extract

Alias of array_element.

list_has

Alias of array_has.

list_has_all

Alias of array_has_all.

list_has_any

Alias of array_has_any.

list_indexof

Alias of array_position.

list_intersect

Alias of array_position.

list_join

Alias of array_to_string.

list_length

Alias of array_length.

list_ndims

Alias of array_ndims.

list_prepend

Alias of array_prepend.

list_pop_back

Alias of array_pop_back.

list_pop_front

Alias of array_pop_front.

list_position

Alias of array_position.

list_positions

Alias of array_positions.

list_push_back

Alias of array_append.

list_push_front

Alias of array_prepend.

list_repeat

Alias of array_repeat.

list_resize

Alias of array_resize.

list_remove

Alias of array_remove.

list_remove_n

Alias of array_remove_n.

list_remove_all

Alias of array_remove_all.

list_replace

Alias of array_replace.

list_replace_n

Alias of array_replace_n.

list_replace_all

Alias of array_replace_all.

list_reverse

Alias of array_reverse.

list_slice

Alias of array_slice.

list_sort

Alias of array_sort.

list_to_string

Alias of array_to_string.

list_union

Alias of array_union.

make_array

Returns an Arrow array using the specified input expressions.

make_array(expression1[, ..., expression_n])

array_empty

Alias of empty.

Arguments

  • expression_n: Expression to include in the output array. Can be a constant, column, or function, and any combination of arithmetic or string operators.

Example

> select make_array(1, 2, 3, 4, 5);
+----------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3),Int64(4),Int64(5)) |
+----------------------------------------------------------+
| [1, 2, 3, 4, 5]                                          |
+----------------------------------------------------------+

Aliases

  • make_list

make_list

Alias of make_array.

string_to_array

Splits a string in to an array of substrings based on a delimiter. Any substrings matching the optional null_str argument are replaced with NULL. SELECT string_to_array('abc##def', '##') or SELECT string_to_array('abc def', ' ', 'def')

starts_with(str, delimiter[, null_str])

Arguments

  • str: String expression to split.
  • delimiter: Delimiter string to split on.
  • null_str: Substring values to be replaced with NULL

Aliases

  • string_to_list

string_to_list

Alias of string_to_array.

trim_array

Removes the last n elements from the array.

DEPRECATED: use array_slice instead!

trim_array(array, n)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • n: Element to trim the array.

range

Returns an Arrow array between start and stop with step. SELECT range(2, 10, 3) -> [2, 5, 8] or SELECT range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);

The range start..end contains all values with start <= x < end. It is empty if start >= end.

Step can not be 0 (then the range will be nonsense.).

Note that when the required range is a number, it accepts (stop), (start, stop), and (start, stop, step) as parameters, but when the required range is a date, it must be 3 non-NULL parameters. For example,

SELECT range(3);
SELECT range(1,5);
SELECT range(1,5,1);

are allowed in number ranges

but in date ranges, only

SELECT range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);

is allowed, and

SELECT range(DATE '1992-09-01', DATE '1993-03-01', NULL);
SELECT range(NULL, DATE '1993-03-01', INTERVAL '1' MONTH);
SELECT range(DATE '1992-09-01', NULL, INTERVAL '1' MONTH);

are not allowed

Arguments

  • start: start of the range
  • end: end of the range (not included)
  • step: increase by step (can not be 0)

Aliases

  • generate_series

Was this helpful?

YesNo

On this page

Array functions·续 - 图1Docs

{"@context":"https://schema.org","@type":"TechArticle","@id":"https://developers.cloudflare.com/basin-pipelines/sql-reference/scalar-functions/array/#page","headline":"Array functions","description":"Scalar functions for manipulating arrays","url":"https://developers.cloudflare.com/basin-pipelines/sql-reference/scalar-functions/array/","inLanguage":"en","image":"https://developers.cloudflare.com/basin-pipelines/sql-reference/scalar-functions/array/og.png?v=11c5330967885c79","dateModified":"2026-10-01","publisher":{"@type":"Organization","name":"Cloudflare","description":"One platform for your apps, agents, and workforce. Build, secure, and scale without managing infrastructure","url":"https://www.cloudflare.com/","sameAs":["https://github.com/cloudflare","https://www.linkedin.com/company/cloudflare","https://x.com/cloudflare"],"logo":{"@type":"ImageObject","url":"https://developers.cloudflare.com/logo.svg"},"address":{"@type":"PostalAddress","streetAddress":"101 Townsend St","addressLocality":"San Francisco","addressRegion":"CA","postalCode":"94107","addressCountry":"US"},"contactPoint":[{"@type":"ContactPoint","contactType":"Customer Support","url":"https://support.cloudflare.com/","availableLanguage":["English"]},{"@type":"ContactPoint","contactType":"Sales","url":"https://www.cloudflare.com/contact/","availableLanguage":["English"]}]},"isPartOf":{"@type":"WebSite","@id":"https://developers.cloudflare.com/#website","name":"Cloudflare Docs","url":"https://developers.cloudflare.com/"}}

description: Scalar functions for manipulating binary strings title: Binary string functions image: https://developers.cloudflare.com/basin-pipelines/sql-reference/scalar-functions/binary-string/og.png?v=960f6441cecb12ac

Skip to content

Documentation Index
Fetch the complete documentation index at: https://developers.cloudflare.com/basin-pipelines/llms.txt
Use this file to discover all available pages before exploring further.