Pinterest Stumbleupon Whatsapp
Advertisement

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 13 Most Important SQL Commands Any Programmer Should Know 13 Most Important SQL Commands Any Programmer Should Know Every big or dynamic website uses a database in some way, and when combined with Structured Query Language (SQL), the possibilities for manipulating data really are endless. Read More , 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 A Basic Guide to JQuery for Javascript Programmers A Basic Guide to JQuery for Javascript Programmers If you are a Javascript programmer, this guide to JQuery will help you start coding like a ninja. Read More 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.

Advertisement

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 6 Easiest Programming Languages to Learn for Beginners 6 Easiest Programming Languages to Learn for Beginners Learning to program is about finding the right language just as much as it's about the edification process. Here are the top six easiest programming languages for beginners. Read More ).

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 What Is An SQL Injection? [MakeUseOf Explains] What Is An SQL Injection? [MakeUseOf Explains] The world of Internet security is plagued with open ports, backdoors, security holes, Trojans, worms, firewall vulnerabilities and a slew of other issues that keep us all on our toes every day. For private users,... Read More , 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 How to Make a Website: For Beginners How to Make a Website: For Beginners Today I'll be guiding you through the process of making a complete website from scratch. Don't worry if this sounds difficult. I'll guide you through it every step of the way. Read More and liven it up with SQL? Or perhaps you need a static site generator 7 Reasons to Ditch Your CMS and Consider a Static Site Generator 7 Reasons to Ditch Your CMS and Consider a Static Site Generator For many years, publishing a website was difficult for many users. CMSs like WordPress changed that, but they can still be confusing. Another alternative is a Static Site Generator. Read More for a simpler approach to building websites.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. DevBachman
    August 29, 2017 at 6:59 pm

    Pipes only work in MySQL if 'sql_mode' is set to PIPES_AS_CONCAT.

    Otherwise use the CONCAT function.