Mysql查询在选择中用空字符串替换NULL [英] MySql Query Replace NULL with Empty String in Select

问题描述

如何用空字符串替换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，当然没有解决问题，还尝试交换when case的位置，这是行不通的. /p>

似乎下面给出的3个解决方案都可以完成任务.问候

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

解决方案

其中一些内置函数应该起作用:

Coalesce
Is Null
IfNull

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

解决方案

Some of these built in functions should work:

Coalesce
Is Null
IfNull

这篇关于Mysql查询在选择中用空字符串替换NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！