fbpx

MySQL String Concatenation

aThis SQL tutorial focuses on SQL String Concatenation in MySQL, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in MySQL. To read additional posts regarding this subject, please use the following links:


MySQL String Concatenation

MySQL String concatenation allows you to append one string to the end of another string. To display the contents of two columns or more under the name of a single column, you can use the MySQL Concat function with the following syntax :

CONCAT(String1,String2,String3...)

For example, to display the book’s name along with its price, use the following MySQL SQL statement:

SELECT CONCAT(bookName,bookPrice)
FROM books

The result :

CONCAT(bookName, bookPrice)
--------------------------
Dune30

MySQL – Concatenate Two Fields with a Space

While in the previous example, the requested result – merging two values from two different columns – has been achieved, the end result is still quite unreadable, as we have no space seperator between the book’s name and its price. Therefore, it is advisable to also concatenate a space (‘ ‘) :

SELECT CONCAT(bookName , ' ' , bookPrice)
FROM books

The result :

CONCAT(bookName, ' ' , bookPrice)
---------------------------------
Dune 30

Using an MySQL Column Alias

To make the result more readable, use n MySQL Column Aliases:

SELECT CONCAT(bookName , ' ' , bookPrice) AS 'bookDescription'
FROM books

The Result :

bookDescription
------------------
Dune 30

MySQL – Retrieving additional columns

In MySQL, If after or before this concatenation you would like to display an additional separate column, simply use a comma (,):

SELECT book_id, CONCAT(bookName , ' ' , bookPrice) AS "bookDescription", 
       author
FROM books

The result:

book_id       bookDescription      author
----------    ------------------   -------------
23            Dune 30               Frank Herbert

MySQL – Concatenate more than two values

In MySQL it is possible to create more complicated concatenations as required:

SELECT CONCAT('book Name : ', bookName , ', and book price : ', bookPrice) AS "B_DETAILS"
FROM books

The result :

B_DETAILS
-------------------------------------------
book Name : Dune, and book price : 30

Using CONCAT_WS

Another MySQL function used to concatenate strings is CONCAT_WS, this function is a special form of the CONCAT function, and in addition to the string concatenation, with CONCAT_WS you are able to determine which character (or characters) will be used as a separator.

For example :
-- Using Hyphen as a separator
SELECT CONCAT_WS('-', bookName, author) AS 'BookDetails'
FROM books

BookDetails
-----------------
Dune-Frank Herbert

-- Using space as a separator
SELECT CONCAT_WS(' ', bookName, author) AS 'BookDetails'
FROM books 

BookDetails
-----------------
Dune Frank Herbert

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…