.net SQLite 等价于 ISNULL()、NVL()、IFNULL() 或 COALESCE()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/799375/
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
SQLite equivalent to ISNULL(), NVL(), IFNULL() or COALESCE()
提问by Jason Down
I'd like to avoid having many checks like the following in my code:
我想避免在我的代码中进行许多如下检查：
myObj.someStringField = rdr.IsDBNull(someOrdinal)
? string.Empty
: rdr.GetString(someOrdinal);
I figured I could just have my query take care of the nulls by doing something like this:
我想我可以通过执行以下操作让我的查询处理空值：
SELECT myField1, [isnull](myField1, '')
FROM myTable1
WHERE myField1 = someCondition
I'm using SQLite though and it doesn't seem to recognize the isnullfunction. I've also tried some equivalent ones recognized in other databases (NVL(), IFNULL()and COALESCE()), but SQLite doesn't seem to recognize any of them.
虽然我正在使用 SQLite，但它似乎无法识别该isnull功能。我还尝试了一些在其他数据库（NVL()、IFNULL()和COALESCE()）中识别的等效数据库，但 SQLite 似乎无法识别其中任何一个。
Does anyone have any suggestions or know of a better way to do this. Unfortunately the database doesn't have default values for all fields. Plus, I need to use some LEFT JOINclauses in some cases, where some of the fields returned will be null because the matching record in the LEFT JOINtable will not exist.
有没有人有任何建议或知道更好的方法来做到这一点。不幸的是，数据库没有所有字段的默认值。另外，LEFT JOIN在某些情况下我需要使用一些子句，其中某些返回的字段将为空，因为LEFT JOIN表中的匹配记录将不存在。
回答by SQLMenace
IFNULL, see here: http://www.sqlite.org/lang_corefunc.html#ifnull
IFNULL，见这里：http: //www.sqlite.org/lang_corefunc.html#ifnull
no brackets around the function
函数周围没有括号
回答by Hardik Darji
Try this
尝试这个
ifnull(X,Y)
e.g
例如
select ifnull(InfoDetail,'') InfoDetail; -- this will replace null with ''
select ifnull(NULL,'THIS IS NULL');-- More clearly....
The ifnull()function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull()must have exactly 2 arguments. The ifnull()function is equivalent to coalesce()with two arguments.
该ifnull()函数返回其第一个非 NULL 参数的副本，如果两个参数都是 NULL，则返回 NULL。Ifnull()必须正好有 2 个参数。该ifnull()函数相当于coalesce()带有两个参数。
回答by Mahdi Mohajer
If there is not ISNULL()method, you can use this expression instead:
如果没有ISNULL()方法，则可以使用此表达式代替：
CASE WHEN fieldname IS NULL THEN 0 ELSE fieldname END
This works the same as ISNULL(fieldname, 0).
这与ISNULL(fieldname, 0).
回答by Thilo
Use IS NULLor IS NOT NULLin WHERE-clause instead of ISNULL() method:
在 WHERE 子句中使用IS NULL或IS NOT NULL代替 ISNULL() 方法：
SELECT myField1
FROM myTable1
WHERE myField1 IS NOT NULL
回答by lewdev
For the equivalent of NVL() and ISNULL() use:
对于相当于 NVL() 和 ISNULL() 使用：
IFNULL(column, altValue)
IFNULL(column, altValue)
column: The column you are evaluating.
column: 您正在评估的列。
altValue: The value you want to return if 'column' is null.
altValue: 如果 'column' 为空，则要返回的值。
Example:
例子：
SELECT IFNULL(middle_name, 'N/A') FROM person;
SELECT IFNULL(middle_name, 'N/A') FROM person;
*Note: The COALESCE() function works the same as it does for other databases.
*注意：COALESCE() 函数的工作方式与其他数据库相同。
Sources:
资料来源：
- COALESCE() Function(w3schools)
- SQL As Understood By SQLite(SQLite website)
- COALESCE() 函数(w3schools)
- SQLite 理解的 SQL（SQLite 网站）
回答by Stas Prihod'co
You can easily define such function and use it then:
您可以轻松定义这样的函数并使用它：
ifnull <- function(x,y) {
if(is.na(x)==TRUE)
return (y)
else
return (x);
}
or same minified version:
或相同的缩小版本：
ifnull <- function(x,y) {if(is.na(x)==TRUE) return (y) else return (x);}
