Concat in select formula - snowflake

when I use concat in select formula I will get concat of other column too.

Example:

SELECT
firstname,
surname,
concat(firstname,' ',surname) AS fullname
FROM
employee

Source data:

| firstname | surname |
| John      | Kenedy  |

Output data:

| firstname   | surname | fullname           |
| Kenedy John | Kenedy  | Kenedy Kenedy John |

Am I using concat wrog way?

Hello you have a bad syntax this must be work

SELECT CONCAT(firstname, ' ', surname) as fullname FROM employee;

Result:

+-----------------+
| fullname        |
+-----------------+
| John Kenedy     |
| Abraham Lincoln |
+-----------------+

You can get more info here

better still. don't use concat function. Use the operator || instead. If you use concat(), and you need to concatenate a bunch of things, it gets very ugly very quickly nesting all the concats within each other.

which do you prefer?

select concat('I ', concat('think ', concat('data ', concat('is ', 'fun '))))

-or-

select 'I ' || 'think ' || 'data ' || 'is ' || 'fun '

Your source data firstname column is not the same as your output data firstname column. If you were to run your concat function on the source data as you've presented it, then I believe you would get the results you expect.

Edit 1: Removing duplicate words from a record with SQL

1. Use a SPLIT_TO_TABLE table function to split each part of the concatenation to an individual row
2. Use QUALIFY clause to filter out duplicate words for each flattened record
3. Grouping by the firstname and surname, use a LISTAGG function to concatenate together each unique word using an ORDER BY clause to preserve the order of the words

CREATE OR REPLACE TEMPORARY TABLE TMP_EMPLOYEE
AS
SELECT $1 AS FIRSTNAME
    ,$2 AS SURNAME
FROM VALUES 
('John','Kenedy')
,('Kenedy John','Kenedy')
;

WITH A AS (
  SELECT E.FIRSTNAME
      ,E.SURNAME
      ,STT.SEQ
      ,STT.INDEX
      ,STT.VALUE
  FROM TMP_EMPLOYEE E
      ,LATERAL SPLIT_TO_TABLE(FIRSTNAME || ' ' || SURNAME,' ') STT
  QUALIFY ROW_NUMBER() OVER(PARTITION BY STT.SEQ,STT.VALUE ORDER BY STT.INDEX) = 1
)
SELECT A.FIRSTNAME
    ,A.SURNAME
    ,LISTAGG(A.VALUE,' ') WITHIN GROUP(ORDER BY A.INDEX) AS FULLNAME
FROM A
GROUP BY A.FIRSTNAME,A.SURNAME
;

Notes

• This does not compare any two or more records to each other to find duplicates

Snowflake CONCAT supports multiple arguments:

> Concatenates one or more strings, or concatenates one or more binary values. If any of the values is null, the result is also null.
>
> CONCAT( <expr1> [ , <exprN> ... ] )

The query works "as-is":

CREATE TABLE employee AS SELECT 'John' AS firstname, 'Kennedy' AS surname;

SELECT firstname,
       surname,
       concat(firstname,' ',surname) AS fullname
FROM employee;

Output:

Sidenote: In the past CONCAT function supported only two arguments.

https://community.snowflake.com/s/ideas - "Improve CONCAT function"