There was a drop down which had a huge list of employees. But some of them hadn't name. It was empty. But not NULL in database.
Initial query was as follows.
SELECT emp_id, name from employees order by name asc
But it shows empty options in select list first.
Then I tried to replace name which was NULL using IFNULL function.
SELECT aemp_id, IFNULL(name, concat('Employee - ', emp_id)) as name from employees where name="" order by name asc
But it was failed due to empty values for name field.
Then I checked for empty value for name field.
NULLIF(name, "")
It will return NULL value if it is empty. Then I used COALESCE function.It returns first NULL or NULL if there no-null values in the list. Complete query as follows.
SELECT emp_id, COALESCE(NULLIF(name, ""), concat('Employee - ', emp_id)) as name from employees where name="" order by name asc
enjoy.
Chamath Gunasekara
Comments