user2285831 user2285831 - 1 month ago 5
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)

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

Rik Rik
Answer

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)

Data

CREATE TABLE my_table(
 id int(11) NOT NULL AUTO_INCREMENT,
 surname VARCHAR(20),
 givenname VARCHAR(20),
 PRIMARY KEY (id)
);

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;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

Comments