Presto equivalent of CONCAT_WS

Question

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

score 6 · Accepted Answer · answered Jul 13 '20 at 09:30

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

Gordon Linoff · Answer 2 · 2020-07-13T01:54:46.230

3

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.

edited Jul 13 '20 at 01:54

answered Jul 13 '20 at 01:25

Gordon Linoff

1,242,037
58
646
786

presto doesn't support logical operators on strings – MTT Jul 13 '20 at 01:50
1

@MTT . . . I fixed the concat operator -- and `+` is not a *logical* operator, it is an *arithmetic* operator. – Gordon Linoff Jul 13 '20 at 01:52

Pratham Desai · Answer 3 · 2021-03-08T03:36:58.060

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.

score 0 · Answer 4 · answered Jul 13 '20 at 01:52

0

select concat(col1, ',', col2)

answered Jul 13 '20 at 01:52

MTT

5,113
7
35
61

5

. . This answer is not correct. It does not handle `NULL` values correctly. – Gordon Linoff Jul 13 '20 at 01:55

Presto equivalent of CONCAT_WS

4 Answers4