Presto equivalent of CONCAT_WS

Question

I'm looking for a function in Presto to concat two columns with a separator like underline.

Hally

Answer 1

Your are looking here for the array_join function, see docs.

array_join(x, delimiter, null_replacement) → varchar

Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.

Example:
columns are c1,c2 you can add more of course:

WITH  demo_table (c1,c2) AS 
    (SELECT * FROM  (VALUES  (1,2),(3,4),(5,null),(7,8) ))
SELECT array_join(array[c1,c2], '_', 'NA')
FROM demo_table

Results will be:
1_2
3_4
5_NA
7_8

Owensby

One caveat is that ARRAY elements must be all same type. So if you want to contact strings and numbers together, you'll need to CAST numbers into varchar for it to work. – Deaconry

Answer 2

To handle:

select concat_ws(',', col1, col2)

You can use:

select substr( concat(case when col1 is not null then ',' || col1 else '' end,
                      case when col2 is not null then ',' || col2 else '' end

                     ),
                2
             )

This concatenates the non-NULL values into a string. The resulting string will start with a comma. The substr() removes the first character.

Holyhead

presto doesn't support logical operators on strings – Hally

@Hally . . . I fixed the concat operator -- and + is not a logical operator, it is an arithmetic operator. – Holyhead

Answer 3

This has been added to PrestoSQL (now Trino) a couple of releases back: https://trino.io/docs/current/functions/string.html

concat_ws(string0, string1, ..., stringN) → varchar#
Returns the concatenation of string1, string2, ..., stringN using string0 as a separator. If string0 is null, then the return value is null. Any null values provided in the arguments after the separator are skipped.

concat_ws(string0, array(varchar)) → varchar
Returns the concatenation of elements in the array using string0 as a separator. If string0 is null, then the return value is null. Any null values in the array are skipped.

Sawhorse

Answer 4

select concat(col1, ',', col2) 

Hally

. . This answer is not correct. It does not handle NULL values correctly. – Holyhead