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

Answer 1

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;