Snowflake CONCAT: How to Concatenate Strings
What it does
CONCAT joins together two or more text strings into one. It's like when you tie together pieces of string; each piece is added to the end of the other. You can use this to combine words, sentences, or parts of text to form a new string.
Note on null values: If any of the strings you want to combine is null (which means there's nothing there), then the entire result will also be null. Think of it like a chain: if one link is missing, the whole chain breaks.
Common use cases
- Creating a Full Name: Combine a first name and a last name into a full name.
- Generating an Email Address: Use parts of a person's name to create an email address.
- Address Formatting: Join street, city, and country into a full address.
How to use it
You can simply list the strings you want to join, separated by commas:
_10SELECT CONCAT('Hello, ', 'world!') AS greeting;
This would give you: Hello, world!
Alternative using || operator
You can also use the || operator to concatenate strings, which works similarly but looks a bit different:
_10SELECT 'Hello, ' || 'world!' AS greeting;
This does the same thing, combining the pieces into Hello, world!
Using CONCAT is handy when you want to piece together text in a database, whether it's for creating messages, formatting data, or building identifiers from parts.
Examples
Concatenating two columns
This example combines the first_name and last_name columns to form a full name.
Input
Table: hitchhikers_characters
| id | first_name | last_name | title |
|---|---|---|---|
| 1 | Arthur | Dent | Mr. |
| 2 | Ford | Prefect | Mr. |
| 3 | Zaphod | Beeblebrox | President |
Snowflake SQL Query
_10SELECT_10 id,_10 first_name,_10 last_name,_10 title,_10 CONCAT (first_name, ' ', last_name) AS full_name_10FROM_10 hitchhikers_characters;
Output
| id | first_name | last_name | title | full_name |
|---|---|---|---|---|
| 1 | Arthur | Dent | Mr. | Arthur Dent |
| 2 | Ford | Prefect | Mr. | Ford Prefect |
| 3 | Zaphod | Beeblebrox | President | Zaphod Beeblebrox |
Concatenating three columns
This example will concatenate the title, first_name, and last_name to create a more formal identification for each character.
Input
Table: hitchhikers_characters
| id | first_name | last_name | title |
|---|---|---|---|
| 1 | Arthur | Dent | Mr. |
| 2 | Ford | Prefect | Mr. |
| 3 | Zaphod | Beeblebrox | President |
Snowflake SQL Query
_10SELECT_10 id,_10 first_name,_10 last_name,_10 title,_10 CONCAT (title, ' ', first_name, ' ', last_name) AS formal_name_10FROM_10 hitchhikers_characters;
Output
| id | first_name | last_name | title | formal_name |
|---|---|---|---|---|
| 1 | Arthur | Dent | Mr. | Mr. Arthur Dent |
| 2 | Ford | Prefect | Mr. | Mr. Ford Prefect |
| 3 | Zaphod | Beeblebrox | President | President Zaphod Beeblebrox |
