sql长度(null)

可以使用IFNULL()、ISNULL()、COALESCE()和NVL()函数

SELECT LENGTH(COALESCE(p.firstName,'') + COALESCE(p.lastName,'')) AS Len FROM TableName

或

SELECT LENGTH(ISNULL(p.firstName,'') + ISNULL(p.lastName,'')) AS Len FROM TableName

或

SELECT LENGTH(IFNULL(p.firstName,'') + IFNULL(p.lastName,'')) AS Len FROM TableName

或

SELECT LENGTH(NVL(p.firstName,'') + NVL(p.lastName,'')) AS Len FROM TableName

或

SELECT LENGTH(p.firstName || p.lastName) AS Len FROM TableName

您可以使用 || 操作员:

LENGTH(p.firstName || p.lastName)

示例:

select ('abc'||null||'d') from dual; -- abcd
select length('abc'||null||'d') from dual; -- 4

试试这个

select case when p.firstname is null then 0 else length(p.firstName) end + 
  case when p.lastName is null then 0 else 
  length(nickname) end from p

Oracle通常将空字符串视为NULL:

COALESCE(LENGTH(p.firstName), 0) + COALESCE(LENGTH(p.lastName),0)

我认为这样使用更好。。。

SELECT LENGTH(ISNULL(p.firstName,'') + ISNULL(p.lastName,'')) AS length FROM TableName

注意:Oracle在处理空白字符时表现不同:

---------Oracle
SELECT LENGTH('') , LENGTH(null) FROM DUAL
--result: null,null

---------Postgress
SELECT LENGTH('') , LENGTH(null)
--result: 0,null

---------SQL Server
SELECT LEN('') , LEN(null)
--result: 0,null

---------MySQL
SELECT LENGTH('') , LENGTH(null)
--result: 0,null