Concatenating addres fields in Snowflake

Question

I have sample data with following address fields,

billing_address	billing_city	billing_state	country
12345 216 Street, Suite. A	Lynwood	Washington	USA

The goal is to get the address in following format;

Expected Output

12345 216 Street, Suite A
 Lynwood, Washington
 USA

here is my sql code for it;

select billing_address & BR()
   & billing_city & ", "
   & billing_state & BR()
   & billing_country
from temp_data

I am getting the following error;

Error: invalid identifier '", "'

Can I kindly get some help on how to fix it? thanks

Your text delimiter possibly needs to be single-quote - ie. ' - not double-quote? — Craig, Jul 20 '23 at 23:11
double quotes is an identifier token (aka table name, column name) and single quotes are strings (is Snowflake SQL) but thoise `&` are not valid SQL so perhaps this is not running in snowflake, albeit that looks like a Snowflake error message — Simeon Pilgrim, Jul 20 '23 at 23:13

score 0 · Answer 1 · answered Jul 20 '23 at 23:20

so it seems a mix of pre-processor and SQL:

select 
    '12345 216 Street, Suite. A' as billing_address,
    'Lynwood' as billing_city,
    'Washington' as billing_state,
    'USA' as billing_country,
    billing_address || BR()|| billing_city || ", " || billing_state || BR() || billing_country

but almost as it stand it does give the error you note:

changing to just Snowflake SQL:

select 
    '12345 216 Street, Suite. A' as billing_address,
    'Lynwood' as billing_city,
    'Washington' as billing_state,
    'USA' as billing_country,
    billing_address || '\r\n' || billing_city || ', ' || billing_state || '\r\n' || billing_country;

Concatenating addres fields in Snowflake

1 Answers1