Presto equivalent of CONCAT_WS
12,077
Solution 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
Solution 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.
Solution 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.
Related videos on Youtube
Comments
-
MTT almost 2 years
I'm looking for a function in Presto to concat two columns with a separator like underline.
-
MTT almost 4 yearspresto doesn't support logical operators on strings
-
Gordon Linoff almost 4 years@MTT . . . I fixed the concat operator -- and
+
is not a logical operator, it is an arithmetic operator. -
Gordon Linoff almost 4 years. . This answer is not correct. It does not handle
NULL
values correctly.