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