In this tutorial we will cover how to combine strings using SQL, why it is important, and a step by step example. 

‍

What is Combining Strings and why is it important:

Combining strings is the process of concatenating strings (or pieces of data) from two columns of a data table into one. This is extremely important when you have columns containing data that should be combined. 

‍

Combine Strings Example:

The need to combine multiple strings into a single string is common when working with data. For example, an individual’s first and last names are often stored as separate columns in a table, but for many uses, we want these combined into a single string containing the full name of the individual.

‍

For example, consider the following table (called INDIVIDUALS)

‍

A common task would be to combine these into the individual’s full name

‍

‍

SQL over Excel:

Combining strings in Excel is easy with the CONCATENATE function, but if the data exists in a data warehouse, it needs to be extracted from the warehouse and loaded into the spreadsheet before being loaded back into the data warehouse after processing. This can be time-consuming and is limited by the size of the data that Excel can handle. If the result needs to be stored in the data warehouse, it will be easier and faster to perform this concatenation in the database.

‍

SQL:

In ANSI SQL, concatenation is performed by the string concatenation operator, ||. This means that in any database that supports the standard syntax, we can generate the full name as:

SELECT firstname || ‘ ‘ || lastname AS fullname
FROM INDIVIDUALS

‍

This syntax works in Snowflake, BigQuery, Redshift, PostgreSQL, Oracle, and Microsoft SQL Server.

MySQL and MariaDB do not support the || operator for concatenation. Instead, || is used as the logical OR operator. These databases use CONCAT instead.

SELECT CONCAT(firstname, ‘ ‘, lastname) AS fullname
FROM INDIVIDUALS

‍

Microsoft SQL Server, Snowflake, BigQuery also support the CONCAT operator along with ||. Oracle and Redshift support CONCAT, but the function only takes two arguments. This means the SELECT statement needs to be written as

SELECT CONCAT(firstname, CONCAT(‘ ‘, lastname)) AS fullname
FROM INDIVIDUALS

‍

Conclusion:

All databases contain a concatenation operator to combine strings. Unfortunately, they do not use a common syntax. Using the code above relevant to your database will make it easy to concatenate strings. 

‍

By following the steps above you have combined the power of combining strings with the power of sql to transform data directly in your cloud data warehouse. Please feel free to reach out to a member of our team if you have any additional questions.

‍

‍

‍