Concat Values In MySQL Query(To Handle Null Values)

Question

I am writing a PHP and MySQL application in which i have to concatenate multiple column values into one single column.I would have used the concat() function,but it does not handle null values,and the concat_ws(),which does not return the result in the output i want. What i need can be achieved in the Oracle database like this:

 Select 'The Surname Is'||last_name from employees;

My Issue is how can i achieve this same result with MySQL..without using the above named functions?

Answer 1

A little trick: Use empty string like separator with CONCAT_WS (Some times you wan't insert white spaces)

CONCAT_WS('','The Surname Is:',lastname) 
FROM `employees`

Answer 2

CONCAT with IFNULL:

SELECT
  CONCAT('The Surname Is ', IFNULL(last_name, 'sadly not available'))
FROM `employees`

Answer 3

Use coalesce to concat an empty string

select concat(coalesce(null, ''));

Answer 4

@Minesh: CONCAT_WS does not 'take care' of NULL values. To illustrate this...

CONCAT_WS("~",house.name,house.address,house.type)

In the above example, if house.address is NULL the returned result will not contain a neat double tilda (~~) as expected. It will be a tilda separated list with only 1 tilda. eg "fun House~mansion"