Concat in select formula - snowflake

snowflake concat rows
snowflake substring
snowflake concatenate null values
snowflake concat null
snowflake string interpolation
snowflake coalesce
snowflake cast
snowflake replace

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

CONCAT_WS — Snowflake Documentation, The collation of the result of the function is the highest-precedence collation of the inputs. Examples�. Concatenate three strings with separator: SELECT� CONCAT_WS¶. Concatenates two or more strings, or concatenates two or more binary values. If any of the values is null, the result is also null. The CONCAT_WS operator requires at least two arguments, and uses the first argument to separate all following arguments.

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 '

Concat in select formula - snowflake, Hello you have a bad syntax this must be work. SELECT CONCAT(firstname, ' ', surname) as fullname FROM employee;. Result: 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.

concat, Docs �; Function Reference �; Alphabetical List of Functions �; CONCAT , ||. Categories: is also null. The || operator provides alternative syntax for CONCAT . SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

Current Concat function only does for two fields., Concatenate many columns to form a new json field . The combination of array agg function and object function did job for me. select object_construct(' concat_field', c_custkey||c_name||c_address) from customer limit 10;. string functions ascii char charindex concat concat with + concat_ws datalength difference format left len lower ltrim nchar patindex quotename replace replicate reverse right rtrim soundex space str stuff substring translate trim unicode upper numeric functions abs acos asin atan atn2 avg ceiling count cos cot degrees exp floor log log10 max

Cool Stuff in Snowflake – Part 2: LISTAGG, This blog post talks about the LISTAGG function. The goal of the function is to concatenate values from a column into a delimited list. Let's take� SQL CONCATENATE (appending strings to one another) String concatenation means to append one string to the end of another string. SQL allows us to concatenate strings but the syntax varies according to which database system you are using.

when concatenated using || symbol in SQL join, giving null result, I am using || symbol to concatenate column and using same to compare the But if any of column contains null values, then entire value is getting as NULL. in count due to the null values in snowflake vs hive,it got resolved. Either expression can include a SELECT statement containing set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General Usage Notes in the Set Operators topic.

Comments
  • Concat literally concatenates two strings. It's not doing a smart merge of any kind. If you wanted to merge it you'd have to check if the contents of one field already existed in the next first.
  • You're doing it right. The output is indeed wrong. Double check your source data.
  • The CONCAT() function takes any number of parameters, so usage vs || is just a matter of taste. I use both, but mostly ||. Depending on how Snowflake parses SQL, one of these alternatives may even be transformed into the other under the hood.
  • Then your issue is not with the CONCAT() function, but rather with trying to remove duplicate words from the output. The CONCAT() or || function appends one or more values (of the same data type) to each other. It DOES NOT parse the data in an attempt to remove duplicates. I will amend my answer to provide a solution that should help.
  • There is a way to do this by building a stored procedure using javascript, but I have not written that as of yet.