当列数据为空时,Coalesce函数不起作用 [英] Coalesce function not working when column data is null
问题描述
HI伙计们,
我写了一个存储过程,根据用户电子邮件或电话号码获取客户详细信息。我用coalesce写了这个sp。当用户电子邮件和电话号码列数据存在时,我得到了结果。但是,当列数据为空时,我无法得到结果。
请帮我解决这个问题。
谢谢
我尝试过:
选择 UserId,UserEmail,Code,PhoneNo,Name,UserImage,U.CreatedDateTime,A.Country,U.UserImageUrl,
来自用户U,地址A 其中
[UserEmail] = coalesce(' email@gmail.com',UserEmail)和
[PhoneNo] = coalesce( null ,PhoneNo)和
A.Id = U.AddressId
和 A.CreatedUserId = U.UserId
我用
解决了这个问题pre> COALESCE([Useremail],'')= coalesce(@ Email,[UserEmail],'')和
COALESCE([PhoneNo],'' )= coalesce(@ PiNumber,[PhoneNo],'')
它使用coalesce函数处理空值
此时coalesce
总是返回email@gmail.com
,因为它是非null 。
所以,替换:
[UserEmail] = coalesce('email @ gmail.com',UserEmail )
with:
[UserEmail] = coalesce(UserEmail,'email @ gmail.com')
BTW:
我不明白使用:[PhoneNo] =聚结(NULL,PHONENO)
。这意味着:用第一个非空值PhoneNo
替换null。
将文档检查为an0ther1 [ ^ 。
Coalesce返回表达式中的第一个非null值 - refer;
MSDN - COALESCE(Transact-SQL) [ ^ ]
也许你应该使用ISNULL - 参考;
ISNULL(Transact-SQL)| Microsoft Docs [ ^ ]
亲切的问候
HI guys,
I wrote a stored proc to get the customer details based on User email or Phone number. i used coalesce to wrote this sp. i got the results when User email and phone number columns data exists. but, i am unable to get the results when column data is null.
Please help me to fix this.
Thanks
What I have tried:
select UserId,UserEmail,Code,PhoneNo,Name,UserImage,U.CreatedDateTime,A.Country,U.UserImageUrl, from Users U ,Address A where [UserEmail]=coalesce('email@gmail.com',UserEmail) and [PhoneNo]=coalesce(null,PhoneNo) and A.Id=U.AddressId and A.CreatedUserId=U.UserId解决方案I solved this by using
COALESCE([Useremail],'')=coalesce(@Email,[UserEmail],'') and COALESCE([PhoneNo],'')=coalesce(@PhoneNumber,[PhoneNo],'')
It handles the null values with coalesce function
At this momentcoalesce
returns alwaysemail@gmail.com
, as it is non-null.
So, replace:
[UserEmail]=coalesce('email@gmail.com',UserEmail)
with:
[UserEmail]=coalesce(UserEmail,'email@gmail.com')
BTW:
I don't understand the usage of:[PhoneNo]=coalesce(null,PhoneNo)
. This means: replace null with first non-null value ofPhoneNo
.
Check the documentation as an0ther1[^] mentioned in his anwer.
Coalesce returns the first non-null value from your expression - refer;
MSDN - COALESCE (Transact-SQL)[^]
Perhaps you should be using ISNULL - refer;
ISNULL (Transact-SQL) | Microsoft Docs[^]
Kind Regards
这篇关于当列数据为空时,Coalesce函数不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!