How to merge data from different column when data is not null?

Question

I have a table with different columns like so

CREATE TABLE survey(
id int(11) not null auto_increment,
what_is_your_name VARCHAR(255),
how_old_are_you varchar(20),
your_occupation varchar(255)
...
...
...
)

I need to build an output from the results of the survey table but I only need to concat the values if the column is not null.

So I want the final string to look like this if all the values are provided NAME IS: [what_is_your_name] AGE: [how_old_are_you] OCCUPATION: [your_occupation]

if only 2 values are provided but the [what_is_your_name] is null then the output should be like this NAME IS: [what_is_your_name] OCCUPATION: [your_occupation]

However, I only want to concat the value is the values are not null. Please note that the table above has a lot more question but I have only posted some of the table to explain the problem.

How can I do that in MySQL?

Thanks

Do you really need that in mysql? If so - explire CONCAT() and IFNULL()/COALESCE()/IF() functions — zerkms
– zerkms, Commented Oct 4, 2013 at 5:23
Yes I need that is mysql because I have to generate the string and then insert it into a different table. I am not using a scripting language so it has to be done with SQL. Can you please give me an example of how can I do that? — Jaylen
– Jaylen, Commented Oct 4, 2013 at 5:30
In my comment after a question you successfully answered there is a list of functions you need to be aware of to accomplish your task. Did you intentionally miss them expecting we do all the job for you? — zerkms
– zerkms, Commented Oct 4, 2013 at 5:31

peterm · Accepted Answer · 2013-10-04 05:31:50Z

2

You can do something like this

SELECT CONCAT_WS(' ', 
         COALESCE(CONCAT('NAME: ', what_is_your_name), ''),
         COALESCE(CONCAT('AGE: ', how_old_are_you), ''),
         COALESCE(CONCAT('OCCUPATION: ', your_occupation), '')) survey_details
  FROM survey

Sample output:

+---------------------------------------------------+
| survey_details                                    |
+---------------------------------------------------+
| NAME: john AGE: 35 OCCUPATION: Software Developer |
| NAME: mark  OCCUPATION: Unemployed                |
| NAME: helen AGE: 35                               |
+---------------------------------------------------+

Here is SQLFiddle demo

answered Oct 4, 2013 at 5:31

peterm

93k16 gold badges157 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

How to merge data from different column when data is not null?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related