up vote 0 down vote favorite

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
share|improve this question
1  
I think (prereq IS NULL) should just read NULL – ScottJShea Mar 5 at 1:51
feedback

6 Answers

up vote 2 down vote

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

select IFNULL(prereq,"") from test
share|improve this answer
+1 this is the simplest answer IMO – Simon Mar 5 at 1:58
feedback
up vote 1 down vote 
select coalesce(prereq, '') from test

Coalesce will return the first non-null arguement passed to it from left to right. If all arguemnts are null, it'll return null, but we're forcing an empty string there, so no null values will be returned.

share|improve this answer
feedback
up vote 0 down vote

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

SELECT prereq FROM test WHERE prereq IS NOT NULL;
share|improve this answer
This is filtering the output which i dont think the OP is after – Simon Mar 5 at 1:57
This is my solution based on his example. I don't really see the reason blank lines would be more professional than NULL values. – Hunter McMillen Mar 5 at 1:59
feedback
up vote 0 down vote

Try below ; 

  select if(prereq IS NULL ," ",prereq ) from test
share|improve this answer
feedback
up vote 0 down vote

Try COALESCE. It returns the first non-NULL value.

SELECT COALESCE(`prereq`, ' ') FROM `test`
share|improve this answer
feedback
up vote 0 down vote accepted

Some of these built in functions should work:

Coalesce Is Null IfNull

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.