Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/to_array below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

TO_ARRAY | Snowflake Documentation

Categories:

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

Converts the input expression to an ARRAY value.

expr

An expression of any data type.

This function returns a value of type ARRAY or NULL:

• If the input is an ARRAY, or a VARIANT containing an ARRAY value, the value is returned unchanged.

• If expr is a NULL or JSON null value, the function returns NULL.

• For any other value, the value returned is a single-element array that contains this value.

To create an array that contains more than one element, you can use ARRAY_CONSTRUCT or STRTOK_TO_ARRAY.

Create a table, and insert data by calling the TO_ARRAY function:

CREATE OR REPLACE TABLE array_demo_2 (
  ID INTEGER,
  array1 ARRAY,
  array2 ARRAY);

INSERT INTO array_demo_2 (ID, array1, array2)
  SELECT 1, TO_ARRAY(1), TO_ARRAY(3);

SELECT * FROM array_demo_2;

Copy

+----+--------+--------+
| ID | ARRAY1 | ARRAY2 |
|----+--------+--------|
|  1 | [      | [      |
|    |   1    |   3    |
|    | ]      | ]      |
+----+--------+--------+

Execute a query that shows the single-element arrays created during the insert and the result of calling ARRAY_CAT to concatenate the two arrays:

SELECT array1, array2, ARRAY_CAT(array1, array2)
  FROM array_demo_2;

Copy

+--------+--------+---------------------------+
| ARRAY1 | ARRAY2 | ARRAY_CAT(ARRAY1, ARRAY2) |
|--------+--------+---------------------------|
| [      | [      | [                         |
|   1    |   3    |   1,                      |
| ]      | ]      |   3                       |
|        |        | ]                         |
+--------+--------+---------------------------+

This example demonstrates that TO_ARRAY converts a string input expression to an array with a single element, even when the input expression includes delimiters (such as commas):

SELECT TO_ARRAY('snowman,snowball,snowcone') AS to_array_result;

Copy

+-------------------------------+
| TO_ARRAY_RESULT               |
|-------------------------------|
| [                             |
|   "snowman,snowball,snowcone" |
| ]                             |
+-------------------------------+

To convert the same string input expression into an array with multiple elements, you can use the STRTOK_TO_ARRAY function:

SELECT STRTOK_TO_ARRAY('snowman,snowball,snowcone', ',') AS strtok_to_array_result;

Copy

+------------------------+
| STRTOK_TO_ARRAY_RESULT |
|------------------------|
| [                      |
|   "snowman",           |
|   "snowball",          |
|   "snowcone"           |
| ]                      |
+------------------------+

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4