5 Ways to Convert a List to a String in DuckDB

If we have a list in DuckDB, and we want to convert it to a string, there are a number of options on the table. Basically, what we want to do is combine/concatenate all elements into a single string. Let’s take a look at five ways to convert a list to a string in DuckDB.

1. The list_string_agg() Function

The list_string_agg() function does exactly what we want; it combines all elements of a list into a single string.

Example:

SELECT list_string_agg(['Cat', 'Dog', 'Duck']);

Result:

Cat,Dog,Duck

Let’s compare it to the original list when it’s saved as a variable:

SET VARIABLE list = ['Cat', 'Dog', 'Duck'];
SELECT 
    getvariable('list') AS original_list,
    list_string_agg(getvariable('list')) AS concatenated_list;

Result:

+------------------+-------------------+
|  original_list   | concatenated_list |
|    VARCHAR[]     |      VARCHAR      |
+------------------+-------------------+
| [Cat, Dog, Duck] | Cat,Dog,Duck      |
+------------------+-------------------+

When run in duckbox mode in the DuckDB CLI, we can see the return types. We can also use the typeof() function to check the type of each result:

SET VARIABLE list = ['Cat', 'Dog', 'Duck'];
SELECT 
    typeof(getvariable('list')) AS original_list_type,
    typeof(list_string_agg(getvariable('list'))) AS concatenated_list_type;

Output:

+--------------------+------------------------+
| original_list_type | concatenated_list_type |
+--------------------+------------------------+
| VARCHAR[]          | VARCHAR                |
+--------------------+------------------------+

2. The array_to_string() Function

The array_to_string() function provides another way to convert the list to a string:

SELECT array_to_string(['Cat', 'Dog', 'Duck'], ' ');

Result:

Cat Dog Duck

This function requires two arguments; the first being the list, and the second being the separator. The separator is used to separate each list element in the final string. I used a space here, but we could just as easily have used a comma, hyphen, or any other character or string of characters.

Here it is with another separator:

SELECT array_to_string(['Cat', 'Dog', 'Duck'], ' & ');

Result:

Cat & Dog & Duck

3. The cast() Function

When talking about converting from one data type to another, no discussion is complete without the cast() function. This is typically the “go to” function when it comes to performing data conversions in SQL databases. And converting a list to a string is no exception:

SELECT cast(['Cat', 'Dog', 'Duck'] AS varchar);

Output:

[Cat, Dog, Duck]

You probably noticed that this result is slightly different to the previous results so far. When using cast(), it includes the square brackets in the resulting string. This may or may not be what you want.

If you don’t want the square brackets, you can always trim them off:

SELECT trim(cast(['Cat', 'Dog', 'Duck'] AS varchar), '[]');

Output:

Cat, Dog, Duck

But if you do want to keep the square brackets, then perhaps the cast() function will be your preferred choice.

4. The list_aggregate() Function

The list_aggregate() function allows us to run an aggregate function without calling that aggregate function like we normally would. The way it works is we pass the name of the aggregate function to list_aggregate() as an argument. list_aggregate() then behaves as if it were that function.

So for a scenario like ours, where we want to concatenate all list items into a string, we can pass string_agg to list_aggregate() in order for it to perform a string aggregation on the list elements:

SELECT list_aggregate(['Cat', 'Dog', 'Duck'], 'string_agg');

Output:

Cat,Dog,Duck

We also have the option of specifying a separator:

SELECT list_aggregate(['Cat', 'Dog', 'Duck'], 'string_agg', '-');

Output:

Cat-Dog-Duck

5. The string_agg() Function

The string_agg() function is used to concatenate strings from different rows in a table. So it’s not normally used on lists. But that’s not to say that we can’t use it on our list data. If we unnest the list, it will end up in table format, with each list element occupying its own row. From there we can apply string_agg() against that “table”.

Example:

SELECT string_agg(l)
FROM unnest(['Cat', 'Dog', 'Duck']) AS t(l);

Output:

Cat,Dog,Duck

We can also specify a custom separator:

SELECT string_agg(l, '-')
FROM unnest(['Cat', 'Dog', 'Duck']) AS t(l);

Output:

Cat-Dog-Duck

It’s true that this is slightly more convoluted than the other options, but if you need to unnest your list data for other reasons, then this option might just be worth considering.