Snowflake : Converting an array into a string in Snowflake [ARRAY_TO_STRING]

Comprehensive Guide to Snowflake’s ARRAY_TO_STRING Function

The ARRAY_TO_STRING function in Snowflake is a utility function designed to convert an array into a string format. The function operates by concatenating the array’s elements with a specified delimiter. Understanding this function is critical when dealing with array data that needs to be presented or stored in a string format.

In this article, we will delve into the functionality of the ARRAY_TO_STRING function, demonstrating its use through a practical example with a Data Definition Language (DDL) script and INSERT statement using a table named “freshers_in”.

Understanding the Syntax

The ARRAY_TO_STRING function follows the below syntax:

ARRAY_TO_STRING(<array_expression>, <delimiter>)

<array_expression>: The array which you want to convert into a string.
<delimiter>: The delimiter which you want to use for separating the elements in the array.

Using ARRAY_TO_STRING Function in Practice

Let’s go through an example to grasp how to utilize the ARRAY_TO_STRING function in Snowflake.

Creating a Table using DDL

First, we’ll create a table named ‘freshers_in’ with an ‘id’ field and a ‘skills’ field that will hold an array of strings.

CREATE TABLE freshers_in (
    id INTEGER,
    skills ARRAY
);

Inserting Data into the Table

Next, we’ll insert some data into the ‘freshers_in’ table using the ARRAY_CONSTRUCT function to create arrays of strings.

INSERT INTO freshers_in (id, skills)
SELECT 1, ARRAY_CONSTRUCT('Java', 'Python', 'SQL') UNION ALL
SELECT 2, ARRAY_CONSTRUCT('C++', 'JavaScript', 'React') UNION ALL
SELECT 3, ARRAY_CONSTRUCT('HTML', 'CSS', 'JavaScript');

Using the ARRAY_TO_STRING Function

Now that we have our table and data ready, we can proceed to use the ARRAY_TO_STRING function.

SELECT 
    id, 
    skills, 
    ARRAY_TO_STRING(skills, ', ') AS skills_string
FROM 
    freshers_in;

Output

ID	SKILLS	SKILLS_STRING
1	[   "Java",   "Python",   "SQL" ]	Java, Python, SQL
2	[   "C++",   "JavaScript",   "React" ]	C++, JavaScript, React
3	[   "HTML",   "CSS",   "JavaScript" ]	HTML, CSS, JavaScript

This SELECT statement will return a new column named ‘skills_string’ that contains a string representation of the ‘skills’ array with each element separated by a comma and a space.