• Ask a Question
  • Create a Poll
150
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel

      Presto equivalent of CONCAT_WS

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

      3 Answers

      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.

      Answered by Jonasclarissadorothy on July 16, 2020..
      select concat(col1, ',', col2)  
      Answered by Ernietinamonica on July 16, 2020..

      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

      Answered by Stephencristinadoreen on July 16, 2020..