Snowflake convert string into output

Question

select ' \''||
CAST(NVL(X.TYPE,'') AS VARCHAR(50))||'\
 \''||CAST(NVL(X.ID_1,'') AS VARCHAR(50))||'\
- \''||
---CAST(NVL(X.ID_2,'') AS VARCHAR(50))
CASE WHEN X.ID_2 IS NOT NULL
THEN X.ID_2
WHEN X.ID_2 IS NULL
THEN 'NULL' END
||'\  '

from 
( select ....)

select query returns value for three columns.

Above code gives o/p as 'R'32 - 'NULL

Expected is 'R 32 - NULL'

Can someone help

user_5070879 · Answer

Using CONCAT and COALESCE:

SELECT *,
  CONCAT('\'',COALESCE(X.TYPE,''), ' ' , 
         COALESCE(X.ID_1::TEXT, ''), ' - ' , 
         COALESCE(X.ID_2::TEXT, 'NULL'), '\'') AS res
FROM (SELECT 'R' AS TYPE, 32::INT AS ID_1, NULL AS ID_2) AS X;

Output:

Snowflake convert string into output

1 Answers