像 '%' 不接受 NULL 值 [英] like '%' does not accept NULL value
问题描述
我有根据用户输入构建的查询(通过 html 表单传递).它看起来像(简单的例子):
I have query that is build from user's inputs ( passed via html form). It looks like (simple example):
Select * From [table] Where [table].[column] like '<parameter>'
此参数可能是可选的,因此如果用户将相应的输入字段留空,我会通过 %.它工作正常,直到我遇到 NULL 值.我知道 '%' 匹配符号不为空,但我想在这种情况下将 NULL 视为空字符串.
This parameter may be optional so if user left corresponding input field empty I pass %. It worked fine until I encountered NULL values. I understand that '%' match symbols not null, but i would like to consider NULL as empty string in this case.
我该怎么办?当用户留空输入时更改查询(如何?)或传递另一个符号?
What should I do? Change query (how?) or pass another symbol(s) when user left empty input?
谢谢.
PS.这是现有系统的真正问题,我知道它远非最佳解决方案,但我必须处理它.
PS. It's real problem from existing system and i know it's far from optimal solution, but i have to deal with it.
推荐答案
您可以使用 coalesce
将 null 视为空字符串:
You can use coalesce
to treat null like an empty string:
where COALESCE([table].[column],'') like '<parameter>'
在 SQL Server 上,您也可以使用 IsNull
:
On SQL Server, you can also use IsNull
:
where IsNull([table].[column],'') like '<parameter>'
这篇关于像 '%' 不接受 NULL 值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!