如果存在middlename，则将其修剪为中间首字母，如果middlename为null，则跳过

Question

-- these lines doesn't work
case 
when isnull(null,e.middlename) = null then (e.firstname+' '+e.lastname)
when e.middlename=' ' then (e.firstname+''+e.lastname)
else (e.firstname +" " +cast(left(e.middlename,1) as varchar)+'. 'e.lastname)
end as MedTech
--
-- line below works fine but will be null if middlename is null     
,MedTech2 = f.firstname +' '+cast(left(f.middlename,1) as varchar)+'. '+f.lastname

它在下面有内部连接

Answer 1

这应该适用于任何兼容SQL99的引擎：

SELECT firstname || ' ' || coalesce(substring(middlename from 1 for 1) || ' ', '') || lastname

Answer 2

您可以尝试以下操作-您需要使用'e.middlename is null'检查null

case when e.middlename is null or e.middlename=' ' then e.firstname+' '+e.lastname
else e.firstname +" " +cast(left(e.middlename,1) as varchar)+'. '+e.lastname
end as MedTech