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?

Filip Roséen · Accepted Answer · 2011-12-16 06:40:40Z

28

CONCAT with IFNULL:

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

answered Dec 16, 2011 at 6:40

Filip Roséen

64.2k20 gold badges153 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Joiner · Accepted Answer · 2013-03-26 13:55:15Z

10

@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"

edited Mar 26, 2013 at 13:55

answered Feb 21, 2013 at 8:54

Martin Joiner

3,6573 gold badges27 silver badges53 bronze badges

3 Comments

CME64 Over a year ago

it does remove it along with it's separator, where is the problem in that? it's the neat solution unless you wanted to do something else

Martin Joiner Over a year ago

I consider it bad because the function performs 2 different tasks, the second of which is not suggested by the function name. First it concatenates, great, thanks, that's what I wanted. Then it removes blank string list items, woah, I didn't ask you to do that. The function should be called CONCAT_NN_WS() where the NN stands for "Not Nulls". If I ask my software to just concatenate 5 things into a list, I expect a 5 item list. I don't then expect the software to then say "Oh by the way, I emptied the bins, fed the cat and phoned your mum to invite her for the weekend too"

CME64 Over a year ago

You're right this will be useful and it should have been implemented (keeping this method as well for other purposes).

Minesh · Accepted Answer · 2012-12-11 10:03:13Z

9

You can also use CONCAT_WS function which takes care of NULL values

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

answered Dec 11, 2012 at 10:03

Minesh

2,3021 gold badge14 silver badges22 bronze badges

Comments

ajreal · Accepted Answer · 2011-12-16 06:37:40Z

4

Use coalesce to concat an empty string

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

answered Dec 16, 2011 at 6:37

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Comments

Curlas · Accepted Answer · 2016-07-29 10:45:23Z

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`

answered Jul 29, 2016 at 10:45

Curlas

8995 silver badges14 bronze badges

Collectives™ on Stack Overflow

Concat Values In MySQL Query(To Handle Null Values)

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related