MySql Query Replace NULL with Empty String in Sele

Question

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

Answer 1

Try this, this should also get rid of those empty lines also:

SELECT prereq FROM test WHERE prereq IS NOT NULL;

Answer 2

If you really must output every values including the NULL ones:

select IFNULL(prereq,"") from test

Answer 3

Some of these built in functions should work:

Coalesce
Is Null
IfNull

Answer 4

UPDATE your_table set your_field="" where your_field is null