MySql Query Replace NULL with Empty String in Select

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

Some of these built-in functions should work:

COALESCE(value,…)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

IS NULL

Tests whether a value is NULL.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

Leave a Reply

Your email address will not be published. Required fields are marked *