How to Build a Query Using the SQL Concatenate Function

When working with queries, sometimes we need to take raw data from our database and reformat it in a different way for use elsewhere. In this article, we look at a couple of functions in MySQL to help us work with string types in our queries. 

Building the Schema

This section is devoted to preparing a quick schema to illustrate the SQL concepts covered in this post. We use MySQL for the functions – I recommend using SQL Fiddle to practice using syntax and running your search queries. There, you can also test your queries in other relational databases.

Create Table and Insert Values

CREATE TABLE birthdays (
 name varchar(200) NOT NULL,
 birthday varchar(10) NOT NULL
);
 
INSERT INTO birthdays (name, birthday) VALUES("Jane", "11/20/1993");
INSERT INTO birthdays (name, birthday) VALUES("Duncan", "01/15/1987");
INSERT INTO birthdays (name, birthday) VALUES("Lucas", "07/21/1996");
INSERT INTO birthdays (name, birthday) VALUES("Alexa", "12/31/1988");

The top four lines create the table “birthdays” with the columns “name” and “birthday”. The rest inserts values into the database. We use this when we build our query statements. 

If we run a basic query on our table, this is what the query statement and the results would look like:

SELECT name, birthday FROM birthdays;

Results:

Building the Query Statements

CONCAT()

The CONCAT() function returns the result after joining the arguments into one string. There is at least one argument, but there may be multiple functions. Arguments in the function could be any one of the following:

• Binary String – If any one of the arguments is a binary string, the return value is a binary string. 

• Nonbinary String – A typical string value. If all arguments are nonbinary string values, CONCAT() returns a nonbinary string value.

• Numeric – If one input is of a numeric type, it is coerced to a nonbinary string before it’s joined with the other arguments. 

• Null – If there are NULL values, those values are simply ignored. 

The formal syntax is as follows: 

CONCAT( <str value 1>, <str value 2>, [ … <str value N> ]);

We use the CONCAT keyword with a pair of parentheses to indicate it’s a function. Inside the parentheses is N input strings. Here’s a it looks using MySQL: 

SELECT CONCAT(name, " ", birthday) AS Result
FROM birthdays;

We “SELECT” the return value from the concatenation of name, a space char, and birthday and assign it the name “Result”. 

The result of this query is: 

Compare this result to the original query statement we tested with in the Building the Schema section. How does it differ? What would be an advantage to setting data this way as opposed to the given way? 

There’s necessarily no right or wrong answer. It’s all about how you rely on this data in your application.

CONCAT_WS()

The CONCAT_WS() function is the same as the CONCAT() function, however, there is a required first argument that’s not in the original CONCAT() function. This argument is the separator that goes in between each of the rest of the arguments in the function. The rest of the rules of CONCAT() apply.

The formal syntax is as follows: 

CONCAT_WS(<separator>, <str value 1>, <str value 2>, [ … <str value N> ]);

We use the CONCAT_WS() keyword with a pair of parentheses to indicate it’s a function. The first argument inside the parentheses is the separator that goes between each input string. The rest of the arguments are strings we join together. Here’s a look using MySQL: 

SELECT CONCAT_WS(", ", name, birthday) AS Result
FROM birthdays;

The result is string concatenation with the first argument in between each of the other arguments.

Like the CONCAT() function, the CONCAT_WS() function returns only one column, named Result, where the value is the input string(s) concatenated with the separator character. 

Conclusion

In this article, we cover the SQL CONCAT() and CONCAT_WS() functions as they relate to its use in MySQL. Be aware there might be minor differences in syntax when using other relational databases as they might have their own flavor of SQL. The concept of the function is the same, but it might be differently worded.