snowflake-cloud-data-platformquotename

Snowflake equivalent for Quotename() in SQL Server?

What would be the equivalent function for quotename() ?

Below is part of a stored procedure:

declare @numcols nvarchar(max)

select @numcols = string_agg(quotename(NAME), ', ') within group (order by NAME)
from 
    (select distinct NAME from #parsednum) as names

I need to convert this code to snowflake equivalent where #parsednum is a temp table. Thanks in advance

Solution

  • Because QUOTENAME() is so specific to SQL Server in that it turns strings into sql server object names, there won't be a 1:1 replacement in Snowflake. If the square brackets are important for encapsulation you could use array_agg() and array_to_string() functions to get close to what your string_agg(quotename()) is doing:

    '[' || ARRAY_TO_STRING(ARRAY_AGG(NAME), '], [') || ']'

    The nice thing about this is that if you don't want square brackets as your encapsulation character, you could change this really easily to anything you like, like double quotes:

    '"' || ARRAY_TO_STRING(ARRAY_AGG(NAME), '", "') || '"'

    The caveat here is that QUOTENAME() does more than just encapsulate. It also escapes control characters inside of the string. If that is important, you can hit your NAME column with some regex before running through the ARRAY_TO_STRING(ARRAY_AGG()) functions.