Categories:

Semi-structured and structured data functions (Array/Object)

ARRAY_REPEAT

Returns an ARRAY value containing a specified number of copies of an element.

Syntax

ARRAY_REPEAT( <element> , <count> )

Arguments

element

The value to repeat in the output array.

The value can be of any semi-structured data type — for example, VARIANT, ARRAY, OBJECT — or any standard Snowflake data type — for example, NUMBER, VARCHAR, BOOLEAN, DATE.

Structured types, such as MAP, aren’t supported.

count

An INTEGER expression specifying the number of times to repeat element.

Returns

The function returns a semi-structured ARRAY value containing count copies of element.

If count is NULL, the function returns NULL.

Usage notes

  • If count is 0 or a negative number, the function returns an empty ARRAY.
  • If element is NULL, the function returns an ARRAY of count NULL values.
  • The element value is implicitly converted to VARIANT in the resulting ARRAY.

Examples

The following example repeats an INTEGER value three times:

SELECT ARRAY_REPEAT(42, 3);
+---------------------+
| ARRAY_REPEAT(42, 3) |
%---------------------%
| [                   |
|   42,               |
|   42,               |
|   42                |
| ]                   |
+---------------------+

The following example repeats a STRING value:

SELECT ARRAY_REPEAT('hello', 2);
+--------------------------+
| ARRAY_REPEAT('hello', 2) |
%--------------------------%
| [                        |
|   "hello",               |
|   "hello"                |
| ]                        |
+--------------------------+

The following example repeats an ARRAY value to create a nested ARRAY:

SELECT ARRAY_REPEAT([1, 2], 2);
+-------------------------+
| ARRAY_REPEAT([1, 2], 2) |
%-------------------------%
| [                       |
|   [                     |
|     1,                  |
|     2                   |
|   ],                    |
|   [                     |
|     1,                  |
|     2                   |
|   ]                     |
| ]                       |
+-------------------------+

The following example shows that a count of 0 returns an empty ARRAY value:

SELECT ARRAY_REPEAT('x', 0);
+----------------------+
| ARRAY_REPEAT('x', 0) |
%----------------------%
| []                   |
+----------------------+

The following example shows that a NULL count returns NULL:

SELECT ARRAY_REPEAT('hi', NULL);
+--------------------------+
| ARRAY_REPEAT('hi', NULL) |
%--------------------------%
| NULL                     |
+--------------------------+