MySql Query Replace NULL with Empty String in Select

Question

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

I think `(prereq IS NULL)` should just read `NULL` – ScottJShea Mar 05 '12 at 01:51 — ScottJShea, Mar 05 '12 at 01:51

score 139 · Answer 1 · answered Mar 05 '12 at 01:53

139

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

select IFNULL(prereq,"") from test

answered Mar 05 '12 at 01:53

JScoobyCed

10,203
6
34
58

if you are using IFNULL with groupBy, then there should be an alias: select IFNULL(prereq,"") as n from test group by n. – Salar Nov 28 '19 at 07:46

Mosty Mostacho · Answer 2 · 2013-09-09T18:25:55.127

88

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.

edited Sep 09 '13 at 18:25

answered Mar 05 '12 at 01:52

Mosty Mostacho

42,742
16
96
123

1

Agreed it is worth noting the MySQL specificity of IFNULL. It works in the OP case but might not in others. – JScoobyCed Sep 10 '13 at 02:26

score 18 · Answer 3 · answered Mar 05 '12 at 01:52

18

Try below ;

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

answered Mar 05 '12 at 01:52

How do i omit when null? (don't select it?) – Pathros Apr 09 '15 at 20:58

score 16 · Accepted Answer · edited Mar 12 '21 at 02:06

16

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.

edited Mar 12 '21 at 02:06

showdev

28,454
37
55
73

answered Mar 22 '12 at 02:25

Abderrahmane BECHIKH · Answer 5 · 2019-02-19T10:32:39.537

9

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

edited Feb 19 '19 at 10:32

answered Feb 19 '19 at 09:48

Abderrahmane BECHIKH

581
7
17

2

Consider adding an explanation of what this code does. A code sample might be immediately useful, but an explanation is far more useful in the long run. – JustCarty Feb 19 '19 at 10:27

score 3 · Answer 6 · answered Oct 10 '15 at 09:48

3

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;

answered Oct 10 '15 at 09:48

M.Nemes

49
1
4

score 2 · Answer 7 · answered Mar 05 '12 at 01:52

2

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

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

answered Mar 05 '12 at 01:52

SenorAmor

3,351
16
27

score 0 · Answer 8 · answered Mar 02 '23 at 14:37

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

score 0 · Answer 9 · answered Mar 05 '12 at 01:51

0

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

SELECT prereq FROM test WHERE prereq IS NOT NULL;

answered Mar 05 '12 at 01:51

Hunter McMillen

59,865
24
119
170

This is filtering the output which i dont think the OP is after – Simon Mar 05 '12 at 01: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 05 '12 at 01:59

score -2 · Answer 10 · answered Oct 22 '18 at 18:28

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.

MySql Query Replace NULL with Empty String in Select

10 Answers10

Linked

Related