MySQL – Introduction to CONCAT and CONCAT_WS functions
- by Pinal Dave
MySQL supports two types of concatenation functions. They are CONCAT and CONCAT_WS
CONCAT function just concats all the argument values as such
SELECT CONCAT('Television','Mobile','Furniture');
The above code returns the following
TelevisionMobileFurniture
If you want to concatenate them with a comma, either you need to specify the comma at the end of each value, or pass comma as an argument along with the values
SELECT CONCAT('Television,','Mobile,','Furniture');
SELECT CONCAT('Television',',','Mobile',',','Furniture');
Both the above return the following
Television,Mobile,Furniture
However you can omit the extra work by using CONCAT_WS function. It stands for Concatenate with separator. This is very similar to CONCAT function, but accepts separator as the first argument.
SELECT CONCAT_WS(',','Television','Mobile','Furniture');
The result is
Television,Mobile,Furniture
If you want pipeline as a separator, you can use
SELECT CONCAT_WS('|','Television','Mobile','Furniture');
The result is
Television|Mobile|Furniture
So CONCAT_WS is very flexible in concatenating values along with separate.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Tips and Tricks, T SQL
