如何让这个 SQL 语句返回空字符串而不是 NULLS？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/584674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I make this SQL statement return empty strings instead of NULLS?
提问by MetaGuru
LastAccessed=(select max(modifydate) from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid),
CompletedLessons=(select Value from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'),
TotalLessons=100,
TotalNumAvail=100,
TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid AND param='cmi.score.raw')
This is only part of a large select statement used by my ASP.NET Repeaterthat keeps crashing when the values are NULL, I have tried ISNULL()but either it didn't work, or I did it wrong.
这只是我的 ASP.NET 使用的大型 select 语句的一部分，该语句Repeater在值为 时不断崩溃NULL，我已经尝试过，ISNULL()但要么不起作用，要么我做错了。
ISNULL((SELECT max(modifydate) FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid),'') AS LastAccessed,
(...)
（……）
???
？？？
UPDATE: I've tried all these things with returning '', 0, 1, instead of the value that would be null and it still doesn't work, I wonder if the problem is with the Repeater?
更新：我已经尝试了所有这些返回 '', 0, 1, 而不是 null 的值，但它仍然不起作用，我想知道问题是否出在Repeater?
Related Question:
相关问题：
回答by jrcs3
You can use the COALESCEfunction to avoid getting nulls. Basically it returns the first non-null value from the list.
您可以使用COALESCE函数来避免获得空值。基本上它返回列表中的第一个非空值。
SELECT COALESCE(dateField, '') FROM Some_Table
This is an ANSI standard, though I have noticed that it isn't available in Access SQL.
这是一个 ANSI 标准，但我注意到它在 Access SQL 中不可用。
回答by BFree
You can use CASE:
您可以使用案例：
CASE WHEN MyField IS Null THEN ''
ELSE MyField
End As MyField
回答by MikeW
select max(isnull(modifydate, "default date") from scormtrackings where...
will work as long as there is at least one row that satisfies the where clause, otherwise you will still get NULL
只要至少有一行满足 where 子句就可以工作，否则你仍然会得到 NULL
select IsNull( max(modifydate), "default_date") from scormtrackings where ...
should work in all cases
应该适用于所有情况
回答by Data Dave
This works too... hate to admit how often I use it!!
这也有效......讨厌承认我经常使用它！
Declare @LastAccessed varchar(30)
声明@LastAccessed varchar(30)
Select @LastAccessed = max(modifydate) from scormtrackings
从 scormtrackings 中选择 @LastAccessed = max(modifydate)
Set @LastAccessed = isnull(@LastAccessed,'')
设置@LastAccessed = isnull(@LastAccessed,'')
Select @LastAccessed as LastAccessed
选择 @LastAccessed 作为 LastAccessed
回答by Kannan Ramamoorthy
You can use NVL.
您可以使用NVL.
NVL(<possible null value>,<value to return when arg1 is null>)
NVL(<possible null value>,<value to return when arg1 is null>)
回答by Learning
select max(isnull(Date,"default date")) .....
