Concat in select formula - snowflake

Question

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?

Answer 1

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:

FIRSTNAME	SURNAME	FULLNAME
John	Kennedy	John Kennedy

---

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

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

Answer 2

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

Answer 3

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 '

Answer 4

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