在 Select 中将 NULL 替换为空字符串

如何用空字符串替换 select 中的 NULL 值？ 输出“ NULL”值看起来不太专业。

这是非常不寻常的，根据我的语法，我希望它能够工作。 我希望你能解释一下为什么没有。

select CASE prereq WHEN (prereq IS NULL) THEN " " ELSE prereq end from test;

原始表的外观、我想要的内容以及实际打印内容的示例:

original     wanted      what actually prints
--------     ------      ---------------------
value1       value1
NULL                     NULL
value2       value2
NULL                     NULL

正如你所看到的，它所做的与我想要的相反，因此我尝试将 IS NULL 翻转为 IS NOT NULL，当然这并没有修复它。我还尝试交换的立场，当情况下，这是不工作的。

看来下面给出的三个解决方案都能完成任务。

select if(prereq IS NULL ," ",prereq ) from test
select IFNULL(prereq,"") from test
select coalesce(prereq, '') from test
175823 次浏览
小开

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

SELECT prereq FROM test WHERE prereq IS NOT NULL;
小开

Try below ; 

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

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

SELECT COALESCE(`prereq`, ' ') FROM `test`
小开 
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.

小开

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

select IFNULL(prereq,"") from test
小开
最佳答案

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.

小开

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;
小开

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.

小开 
UPDATE your_table set your_field="" where your_field is null
小开 
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


提交答案