CODEWITHSUNDEEP
snowflake-cloud-data-platform
Pandafreak
Pandafreak

Reputation: 107

snowflake concat two fields with space

I got two fields: First_name (e.g.John) and Surname (e.g.Doe) How do I concat these for a join to another table that's Name (e.g. John Doe). Trying contact(First_name, Surname) gives me JohnDoe.

Thanks!

Upvotes: 0

Views: 7202

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

For the cases where you will only have two fields, there are three equally trivial methods, CONCAT, ||, & CONCAT_WS:

select 
    first_name
    ,surname
    ,concat(first_name, ' ', surname) as way1
    ,first_name || ' ' || surname as way2
    ,concat_ws(' ',first_name, surname) as way3
from values
    ('john', 'doe'),
    ('the rock', null) 
    as t(first_name, surname);
FIRST_NAME SURNAME WAY1 WAY2 WAY3
john doe john doe john doe john doe
the rock null null null null

but if any of those tokens are null, then the whole things is null.

If you have more than 2 tokens and non are null, then concat_ws starts to win.

and if you want something that can handle nulls but not have unwanted whitespace. you need to use array_construct_compact/array_to_string:

select 
    one
    ,two
    ,three
    ,concat(one, ' ', two, ' ', three) as way1
    ,one || ' ' || two || ' ' || three as way2
    ,concat_ws(' ',one, two, three) as way3
    ,array_to_string(array_construct_compact(one, two, three), ' ') as way4
from values
    ('john', 'doe', ''),
    ('the rock', null, null),
    ('three','happy', 'words')
    as t(one, two, three);
ONE TWO THREE WAY1 WAY2 WAY3 WAY4
john doe '' john doe john doe john doe john doe
the rock null null null null null the rock
three happy words three happy words three happy words three happy words three happy words

Upvotes: 0

Jaidon S
Jaidon S

Reputation: 21

There looks to be a concat with separator function https://docs.snowflake.com/en/sql-reference/functions/concat_ws.html

Try concat_ws(' ', First_name, Surname);

Upvotes: 2

Related Questions