仅使用空白替换空值

I currently have a sql statement that outputs data into an excel document. However, every time there is a cell that doesn't have data, it outputs NULL into the cell. I was wondering if there was a way that I could replace NULL with just a blank space? Someone suggested using coalesce to do this, however I haven't ever had the chance to use it so I will have to do some reading on it. Anyone have any other suggestions?

我目前有一个sql语句，可以将数据输出到excel文档中。但是，每次有一个没有数据的单元格时，它会向单元格输出NULL。我想知道是否有一种方法可以用空格替换NULL？有人建议使用coalesce来做到这一点，但我没有机会使用它，所以我将不得不做一些阅读。有没有其他建议？

4 个解决方案

#1

In your select you can put an IsNull/IfNull round the column. Not particularly efficient but does what you want.

在您的选择中，您可以在列上放置一个IsNull / IfNull。不是特别有效，但做你想要的。

MS SQL

MS SQL

Select IsNull(ColName, '') As ColName From TableName

MySQL

MySQL的

Select IfNull(ColName, '') As ColName From TableName

#2

IFNULL is an Oracle extension (adopted by many other platforms). The "standard" SQL function is coalesce():

IFNULL是Oracle扩展（许多其他平台采用）。 “标准”SQL函数是coalesce（）：

SELECT COALESCE(columnName, ' ') AS ColumnName
FROM tableName;

#3

MySQL

MySQL的

select IFNULL(columnName, '') from tableName

IFNULL(expr1,expr2)

IFNULL（表达式1，表达式2）

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

如果expr1不为NULL，则IFNULL（）返回expr1;否则返回expr2。 IFNULL（）返回数值或字符串值，具体取决于使用它的上下文。

#4

SELECT teacher.name, IfNull(dept.name, '') as Dept
 FROM teacher left outer JOIN dept
           ON teacher.dept=dept.id