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)

Month Class
Sanchez Carlos
Davis Emily
Moore James
Martinez Isabella
Brown Megan
Garcia Olivia
Lopez Juan
Wilson Emma
Roberts Pamella
Williams Andre

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

Full Name
Carlos Sanchez
Emily Davis
James Moore
Isabella Martinez
Megan Brown
Olivia Garcia
Juan Lopez
Emma Wilson
Pamella Roberts
Andre Williams

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.

No-code/low-code data prep and visualization

Request Demo
Try for Free