如何替换SQL查询中SUBSTRING返回的空值为2？

嘿，这个需求我太熟了，完全不用重复写一堆CASE语句！你可以借助NULLIF和COALESCE的组合来实现，代码会清爽很多。

核心思路 #

SUBSTRING在截取位置超出字符串长度时会返回空字符串，我们可以：

1. 用NULLIF(你的SUBSTRING表达式, '')把空字符串转换成NULL
2. 再用COALESCE(转换后的结果, '2')把NULL替换成你需要的'2'

这样一套组合拳就能替代重复的CASE判断，可读性和维护性都提升不少。

修改后的完整SQL语句 #

SELECT 
    COALESCE(NULLIF(SUBSTRING(LastName,2,1), ''), '2') +
    COALESCE(NULLIF(SUBSTRING(LastName,3,1), ''), '2') +
    COALESCE(NULLIF(SUBSTRING(LastName,5,1), ''), '2') +
    COALESCE(NULLIF(SUBSTRING(FirstName,2,1), ''), '2') +
    COALESCE(NULLIF(SUBSTRING(FirstName,3,1), ''), '2') +
    REPLACE(CONVERT(varchar, DateOfBirth,101),'/','') +
    CASE WHEN GenderID = '1' Then '1' WHEN GenderID = '2' Then '2' ELSE '9' END 
FROM Client

可选简化方案（SQL Server 2012+） #

如果你的SQL Server版本是2012及以上，也可以用IIF函数来写，语法更直观：

SELECT 
    IIF(SUBSTRING(LastName,2,1) = '', '2', SUBSTRING(LastName,2,1)) +
    IIF(SUBSTRING(LastName,3,1) = '', '2', SUBSTRING(LastName,3,1)) +
    IIF(SUBSTRING(LastName,5,1) = '', '2', SUBSTRING(LastName,5,1)) +
    IIF(SUBSTRING(FirstName,2,1) = '', '2', SUBSTRING(FirstName,2,1)) +
    IIF(SUBSTRING(FirstName,3,1) = '', '2', SUBSTRING(FirstName,3,1)) +
    REPLACE(CONVERT(varchar, DateOfBirth,101),'/','') +
    CASE WHEN GenderID = '1' Then '1' WHEN GenderID = '2' Then '2' ELSE '9' END 
FROM Client

不过NULLIF+COALESCE的写法兼容性更强，在SQL Server 2008及更早版本也能正常运行，看你的实际环境选择就好。

内容的提问来源于stack exchange，提问作者Philip