user2285831 user2285831 - 1 year ago 72
MySQL Question

How to use IF condition in select query to display a column that is not null

I want to display a column in a select query according to the following :

1- If result is not null, display the column.

2- If the result is null, do not display the column.

I tried this but it doesn't work, it displays all the time the column (null or not null)

if(surname is null,'',surname) as surname
from my_table

Rik Rik
Answer Source

If you still want an example... here it is.
B.T.W. this example has multiple columns (id and givenname). If you only have that one surname column you could do this a whole other way but it would depend on the rest of your "script" how you would do it. (because the select would not return ANY columns in that case and that's invalid for a select)


CREATE TABLE my_table(
 surname VARCHAR(20),
 givenname VARCHAR(20),

INSERT INTO my_table(givenname, surname) VALUES
("me", null),
("you", null),
(null, null),
("and sombody else", null);

Dynamic SQL

SET @nr_names = (SELECT COUNT(*) FROM my_table WHERE NOT(surname IS null));
SET @fields = (SELECT if(@nr_names=0, "id, givenname", "id, givenname, surname"));
SET @query = CONCAT("select ", @fields, " from my_table");
PREPARE stmt FROM @query;

Will result in

1   me
2   you
3   (null)
4   and sombody else

Chaning the data to

INSERT INTO my_table(givenname, surname) VALUES
("me", null),
("you", "works"),
(null, null),
("and sombody else", null);

You get

1   me                (null)
2   you               works
3   (null)            (null)
4   and sombody else  (null)

Working SQL Fiddle.
(unfortunately SQL Fiddle isn't what it used to be with the lack of speed these days)

I can't get SQL Fiddle to work anymore. Here is the alternative: RexTester
See there is no column surname. Change one null to text in the insert data and click "Run it".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download