Reputation:

MySql Query Replace NULL with Empty String in Select

How do you replace a NULL value in the select with an empty string? It doesn't look very professional to output "NULL" values.

This is very unusual and based on my syntax I would expect it to work. I'm hoping for an explanation why it doesn't.

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 actually 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 didn't fix it. I also tried swapping the position of when case, which did not work.

It seems the 3 solutions given below all do the task.

select if(prereq IS NULL ," ",prereq ) from test
select IFNULL(prereq,"") from test
select coalesce(prereq, '') from test

Upvotes: 97

Answers (10)

user319198

Reputation:

Try this:

select if(prereq IS NULL ," ",prereq ) from test

Upvotes: 19

Mosty Mostacho

Reputation: 43494

SELECT COALESCE(prereq, '') FROM test

Coalesce will return the first non-null argument 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.

Also note that the COALESCE operator is supported in standard SQL. This is not the case of IFNULL. So it is a good practice to get use the former. Additionally, bear in mind that COALESCE supports more than 2 parameters and it will iterate over them until a non-null coincidence is found.

Upvotes: 92

christine

Reputation: 51

SELECT IF(TRIM(COALESCE(prereq, '')) = '', '[ empty ]', field1) FROM test

Query will fill up '[ empty ]' text where prereq column is null or is any length of whitespace

See TRIM COALESCE and IS NULL for more info.

Also Working with null values from the MySQL docs

Upvotes: 0

user1284846

Reputation:

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.

Upvotes: 18

Abderrahmane BECHIKH

Reputation: 653

select IFNULL(`prereq`,'') as ColumnName FROM test

this query is selecting "prereq" values and if any one of the values are null it show an empty string as you like So, it shows all values but the NULL ones are showns in blank

Upvotes: 10

Daniel

Reputation: 1

I have nulls (NULL as default value) in a non-required field in a database. They do not cause the value "null" to show up in a web page. However, the value "null" is put in place when creating data via a web page input form. This was due to the JavaScript taking null and transcribing it to the string "null" when submitting the data via AJAX and jQuery. Make sure that this is not the base issue as simply doing the above is only a band-aid to the actual issue. I also implemented the above solution IFNULL(...) as a double measure. Thanks.

Upvotes: -2

M.Nemes

Reputation: 69

The original form is nearly perfect, you just have to omit prereq after CASE:

SELECT
  CASE
    WHEN prereq IS NULL THEN ' '
    ELSE prereq
  END AS prereq
FROM test;

Upvotes: 3

JScoobyCed

Reputation: 10423

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

select IFNULL(prereq,"") from test

Upvotes: 146

SenorAmor

Reputation: 3345

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

SELECT COALESCE(`prereq`, ' ') FROM `test`

Upvotes: 2

Hunter McMillen

Reputation: 61550

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

SELECT prereq FROM test WHERE prereq IS NOT NULL;

Upvotes: 0

MySql Query Replace NULL with Empty String in Select

Answers (10)

Related Questions