Array functions

- The function will return `1` if array2 is empty.
- `Null` is processed as a value. In other words `hasSubstr([1, 2, NULL, 3, 4], [2,3])` will return `0`. However, `hasSubstr([1, 2, NULL, 3, 4], [2,NULL,3])` will return `1`
- The order of values in both the arrays does matter.

Raises a `NO_COMMON_TYPE` exception if any of the elements of the two arrays do not share a common supertype.

**Syntax**

```
hasSubstr(arr1, arr2)
```

**Arguments**

- `arr1` — Array of any type with a set of elements. [`Array(T)`](/docs/sql-reference/data-types/array)
- `arr2` — Array of any type with a set of elements. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns `1` if array `arr1` contains array `arr2`. Otherwise, returns `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Both arrays are empty**

```
SELECT hasSubstr([], [])
```

```
1
```

**Arrays containing NULL values**

```
SELECT hasSubstr([1, Null], [Null])
```

```
1
```

**Arrays containing values of a different type**

```
SELECT hasSubstr([1.0, 2, 3, 4], [1, 3])
```

```
0
```

**Arrays containing strings**

```
SELECT hasSubstr(['a', 'b'], ['a'])
```

```
1
```

**Arrays with valid ordering**

```
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'b'])
```

```
1
```

**Arrays with invalid ordering**

```
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'c'])
```

```
0
```

**Array of arrays**

```
SELECT hasSubstr([[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4]])
```

```
1
```

**Arrays without a common type**

```
SELECT hasSubstr([1, 2, NULL, 3, 4], ['a'])
```

```
Raises a `NO_COMMON_TYPE` exception
```

## indexOf[​](#indexOf "Direct link to indexOf")

Introduced in: v1.1.0

Returns the index of the first element with value 'x' (starting from 1) if it is in the array.
If the array does not contain the searched-for value, the function returns `0`.

Elements set to `NULL` are handled as normal values.

**Syntax**

```
indexOf(arr, x)
```

**Arguments**

- `arr` — An array to search in for `x`. [`Array(T)`](/docs/sql-reference/data-types/array)
- `x` — Value of the first matching element in `arr` for which to return an index. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the index (numbered from one) of the first `x` in `arr` if it exists. Otherwise, returns `0`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic example**

```
SELECT indexOf([5, 4, 1, 3], 3)
```

```
4
```

**Array with nulls**

```
SELECT indexOf([1, 3, NULL, NULL], NULL)
```

```
3
```

## indexOfAssumeSorted[​](#indexOfAssumeSorted "Direct link to indexOfAssumeSorted")

Introduced in: v24.12.0

Returns the index of the first element with value 'x' (starting from `1`) if it is in the array.
If the array does not contain the searched-for value, the function returns `0`.

Note

Unlike the `indexOf` function, this function assumes that the array is sorted in
ascending order. If the array is not sorted, results are undefined.

**Syntax**

```
indexOfAssumeSorted(arr, x)
```

**Arguments**

- `arr` — A sorted array to search. [`Array(T)`](/docs/sql-reference/data-types/array)
- `x` — Value of the first matching element in sorted `arr` for which to return an index. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the index (numbered from one) of the first `x` in `arr` if it exists. Otherwise, returns `0`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic example**

```
SELECT indexOfAssumeSorted([1, 3, 3, 3, 4, 4, 5], 4)
```

```
5
```

## kql\_array\_sort\_asc[​](#kql_array_sort_asc "Direct link to kql_array_sort_asc")

Introduced in: v23.10.0

Sorts one or more arrays in ascending order. The first array is sorted, and subsequent arrays are reordered to match the first array's sorted order. Null values are placed at the end. This is a KQL (Kusto Query Language) compatibility function.

**Syntax**

```
kql_array_sort_asc(array1[, array2, ..., nulls_last])
```

**Arguments**

- `array1` — The array to sort. [`Array(T)`](/docs/sql-reference/data-types/array)
- `array2` — Optional. Additional arrays to reorder according to array1's sort order. [`Array(T)`](/docs/sql-reference/data-types/array)
- `nulls_last` — Optional. A boolean indicating whether nulls should appear last. Default is true. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple of arrays sorted in ascending order. [`Tuple(Array, ...)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
SELECT kql_array_sort_asc([3, 1, 2])
```

```
([1, 2, 3])
```

## kql\_array\_sort\_desc[​](#kql_array_sort_desc "Direct link to kql_array_sort_desc")

Introduced in: v23.10.0

Sorts one or more arrays in descending order. The first array is sorted, and subsequent arrays are reordered to match the first array's sorted order. Null values are placed at the end. This is a KQL (Kusto Query Language) compatibility function.

**Syntax**

```
kql_array_sort_desc(array1[, array2, ..., nulls_last])
```

**Arguments**

- `array1` — The array to sort. [`Array(T)`](/docs/sql-reference/data-types/array)
- `array2` — Optional additional arrays to reorder according to array1's sort order. [`Array(T)`](/docs/sql-reference/data-types/array)
- `nulls_last` — Optional boolean indicating whether nulls should appear last. Default is true. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple of arrays sorted in descending order. [`Tuple(Array, ...)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
SELECT kql_array_sort_desc([3, 1, 2])
```

```
([3, 2, 1])
```

## length[​](#length "Direct link to length")

Introduced in: v1.1.0

Calculates the length of a string or array.

- For String or FixedString arguments: calculates the number of bytes in the string.
- For Array arguments: calculates the number of elements in the array.
- If applied to a FixedString argument, the function is a constant expression.

Please note that the number of bytes in a string is not the same as the number of
Unicode "code points" and it is not the same as the number of Unicode "grapheme clusters"
(what we usually call "characters") and it is not the same as the visible string width.

It is ok to have ASCII NULL bytes in strings, and they will be counted as well.

**Syntax**

```
length(x)
```

**Aliases**: `OCTET_LENGTH`

**Arguments**

- `x` — Value for which to calculate the number of bytes (for String/FixedString) or elements (for Array). [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) or [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the number of number of bytes in the String/FixedString `x` / the number of elements in array `x` [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**String example**

```
SELECT length('Hello, world!')
```

```
13
```

**Array example**

```
SELECT length(['Hello', 'world'])
```

```
2
```

**constexpr example**

```
WITH 'hello' || toString(number) AS str
SELECT str,
isConstant(length(str)) AS str_length_is_constant,
isConstant(length(str::FixedString(6))) AS fixed_str_length_is_constant
FROM numbers(3)
```

```
┌─str────┬─str_length_is_constant─┬─fixed_str_length_is_constant─┐
│ hello0 │                      0 │                            1 │
│ hello1 │                      0 │                            1 │
│ hello2 │                      0 │                            1 │
└────────┴────────────────────────┴──────────────────────────────┘
```

**unicode example**

```
SELECT 'ёлка' AS str1, length(str1), lengthUTF8(str1), normalizeUTF8NFKD(str1) AS str2, length(str2), lengthUTF8(str2)
```

```
┌─str1─┬─length(str1)─┬─lengthUTF8(str1)─┬─str2─┬─length(str2)─┬─lengthUTF8(str2)─┐
│ ёлка │            8 │                4 │ ёлка │           10 │                5 │
└──────┴──────────────┴──────────────────┴──────┴──────────────┴──────────────────┘
```

**ascii\_vs\_utf8 example**

```
SELECT 'ábc' AS str, length(str), lengthUTF8(str)
```