How do you replace a NULL value in the select with an empty string? It doesnt look very professional to output “NULL” values.
This is very unusual and based on my syntax I would expect it to work. Hoping for an explanation why it doesnt.
select CASE prereq WHEN (prereq IS NULL) THEN " " ELSE prereq end from test;
Example of what the original table looks like, what I want, and what actual prints:
original wanted what actually prints -------- ------ --------------------- value1 value1 NULL NULL value2 value2 NULL NULL
As you can see it does the opposite of what I want, hence I tried flipping the IS NULL to IS NOT NULL and of course that didnt fix it, also tried swapping the position of when case, which did not work.
Edit: It seems the 3 solutions given below all do the task. regards
select if(prereq IS NULL ," ",prereq ) from test select IFNULL(prereq,"") from test select coalesce(prereq, '') from test
Some of these built-in functions should work:
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
Tests whether a value is NULL.
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.