CODEWITHSUNDEEP
snowflake-cloud-data-platform
Rahul
Rahul

Reputation: 23

Snowflake convert string into output

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

Upvotes: 2

Views: 230

Answers (2)

JayRizzo
JayRizzo

Reputation: 3616

@Rahul, I believe you are looking for something like this? The escaped apostrophes were the culprit from Lukasz's answer.

SELECT 
          CONCAT(
                  COALESCE(X.TYPE,'')
                , CHAR(32)                        /* <-- ADDS A SPACE */
                , COALESCE(X.ID_1::TEXT, '')
                , CHAR(32), CHAR(45), CHAR(32)    /* <-- ADDS A SPACE DASH SPACE */
                , COALESCE(X.ID_2::TEXT, 'NULL')
                ) AS res
FROM (SELECT 'R' AS TYPE, 32::INT AS ID_1, NULL AS ID_2) AS X
;

Returns:

R 32 - NULL

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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:

enter image description here

Upvotes: 2

Related Questions