Structured Query Language (SQL) is a remarkably powerful tool, and one that is packed full of features. Once you've mastered the most important SQL commands, you can start to get a bit more creative with your SQL. Today I'll show you everything you need to know about SQL concatenation strings.

There are many different SQL dialects. For all these examples, I'm using the PostgreSQL variant.

What Is Concatenation?

Concatenation means to join two things together. You may have used it in a programming language to join two strings together. Perhaps you have a first name and surname variables that you joined together as a full name variable.

Concatenation is a very useful way of combining two strings into one. PHP uses a full stop to join strings together, whereas JavaScript and jQuery use a plus sign.

Concatenation in SQL works exactly the same. You use a special operator to join two things into one. Here's an example in Pseudocode: 

        first_name = Joe
last_name = Coburn
whole_name = first_name + last_name

In programming languages, concatenation makes code easier to read. If your code always needs to access two strings, combining them into one makes it easier to remember, and reduces the length of the code.

While variables in SQL are less common (but are still used), concatenation is still needed to return combined results, or to manipulate data.

How to Concatenate

Concatenation is very easy in SQL. While SQL is a common language, individual database engines implement features in different ways. While all these examples are in the PostgreSQL dialect, it's easy to translate to other variants by simply searching the web for "Concatenate <YOUR_DATABASE_ENGINE>." Different engines may have a different syntax for concatenation, but the principle remains the same.

Going back to our name example, here's a basic select query: 

        SELECT first_name, last_name, email FROM users_table
sql select

Nothing complex here, so let's add in the concatenate: 

        SELECT first_name || last_name AS full_name, email FROM users_table
sql basic concatenation

As you can see, this concatenation has worked perfectly, but there's one small problem. The resulting full name has been stitched together exactly as the product of both the columns -- there should be a space between the names!

Fortunately, it's easy to fix: simply concat a space between the two: 

        SELECT first_name || ' ' || last_name AS full_name, email FROM users_table
sql concatenation space

These are basic examples, but you should see how concatenation works -- it really is that easy! The pipe operator (|) is used twice between clauses. Your SQL engine knows that each part before and after this symbol should be joined together and treated as one. Be careful though, if you use the concat operator but don't concatenate anything, you will get an error.

sql error

As mentioned above, these examples use the PostgreSQL variant of SQL. Other variants may use a different operator, or even a special function you have to call. It doesn't really matter how you concatenate strings, providing you do it in the way your database engine is expecting.

Going Deeper

Now that you know the basics, let's look at some in-depth examples, along with some common pitfalls.

Most database engines will successfully concat a mixture of strings and integers, maybe even dates as well. You will usually run into problems when trying to concatenate complex types such as arrays: 

        SELECT first_name || ' ' || last_name || ARRAY[123, 456] AS full_name, email FROM users_table

This code will not work. It's not possible to combine strings with complex objects such as arrays. If you think about what you need to do, you can often write simple code that works, rather than complex, crazy code that fails to run.

If you have thought carefully about what you need to do, and still can't get the SQL to work, then have you considered using a programming language? As a software developer working on legacy code, I know the pain of trying to debug SQL that somebody has crammed so much logic into it's a wonder it runs at all -- if you're trying to write logic in SQL, then switch to a programming language (there are plenty of easy languages to learn).

Concatenation works very well for where statements as well: 

        SELECT first_name, last_name, email FROM users_table WHERE date_of_birth = ('DAY' || '/' || 'MONTH' || '/' || 'YEAR')::date

There's a few things happening here. In this example, DAY, MONTH, and YEAR are parameters that have been passed in from a script. Maybe these have been generated by code, or entered by a user. These are concatenated together, and then cast to a date type (using the PostgreSQL cast to date syntax ::date).

Using concatenation this way allows you to chain together the individual parts of a date, which can then be processed as a "real" date, as opposed to a string. Don't forget that this basic example does not protect against SQL injection, so don't go using it in any production code without modifying.

Another pitfall to watch out for is null values (a null string is an empty or non existing string). Given this query: 

        SELECT first_name || ' ' || NULL AS full_name, email FROM users_table
sql null concatenation

This query silently fails. This is due to the way concatenation is internally coded on your database engine. You may not always encounter this problem, but it's quite a common occurrence.

If you think the data your query returns may be null, then you'll need to use a coalesce. Coalesce can roughly be thought of as "if this is null, replace it with this other string or column": 

        SELECT first_name || ' ' || COALESCE(NULL, 'ERROR NULL DATA') AS full_name, email FROM users_table
sql concatenation coalesce

Now you know how to use concatenation in SQL, what will you do with it? Will you make a website and liven it up with SQL? Or perhaps you need a static site generator for a simpler approach to building websites.

Whatever you do, let us know in the comments below!