SQLite等价于ISNULL()、NVL()、IFNULL()或合并()

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:

我想我可以让我的查询通过这样的方式来处理null值:

SELECT myField1, [isnull](myField1, '') 
FROM myTable1
WHERE myField1 = someCondition

I'm using SQLite though and it doesn't seem to recognize the isnull function. 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()和合并())中识别的等价类，但是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 JOIN clauses in some cases, where some of the fields returned will be null because the matching record in the LEFT JOIN table will not exist.

有没有人有什么建议或者知道更好的方法。不幸的是，数据库没有所有字段的默认值。另外，在某些情况下，我需要使用一些左连接子句，其中返回的一些字段将为空，因为左侧联接表中的匹配记录将不存在。

5 个解决方案

#1

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

函数没有括号。

#2

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.

函数的作用是:返回第一个非空参数的副本，如果两个参数都为空，则返回NULL。Ifnull()必须有两个参数。ifnull()函数等价于两个参数合并。

#3

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)相同。

#4

Use IS NULL or IS NOT NULL in WHERE-clause instead of ISNULL() method:

在where子句中使用NULL或NOT NULL，而不是ISNULL()方法:

SELECT myField1
FROM myTable1
WHERE myField1 IS NOT NULL

#5

-2

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);}

#1