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:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
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.
-- 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
