SQL Server Express 2008 R2 - 文本字段中的方括号不返回结果
我创建了一个带有一些默认值的简单存储过程,以启用“MachineModel”数据库的自定义搜索。
作为 WHERE 子句的一部分,我有这样的内容:
Model.[ModelName] LIKE '%' + ISNULL(@ModelName, Model.[ModelName]) + '%'
它应该允许部分匹配。这适用于大多数模型,但是如果没有为 @ModelName 提供任何值(因此 ISNULL 解析为 Model.[ModelName])并且底层字段数据包含方括号,例如“Lenny's Test Model [2010]”,那么 SProc 不会返回这些记录。
这并不是一个大问题,因为实际上只有 4 个模型(约 120,000 个)的名称中包含方括号,因此我可以轻松更改它们,但是我很想知道发生了什么以及解决此问题的最佳方法是什么。
干杯,
莱尼。
I've created a simple stored procedure with some default values to enable customised searching of my "MachineModel" database.
As part of the WHERE clause I have something like this:
Model.[ModelName] LIKE '%' + ISNULL(@ModelName, Model.[ModelName]) + '%'
which should allow for partial matches. This works for the majority of models, however if no value is supplied for @ModelName (and therefore the ISNULL resolves to Model.[ModelName]) and the underlying field data contains square brackets, such as "Lenny's Test Model [2010]", then those records records aren't returned by the SProc.
This isn't a huge problem as only 4 models (of about 120,000) actually have square brackets in their names so I could easily change them, however I'm curious to know what's going on and what's the best way to solve this.
Cheers,
Lenny.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要将前括号替换为
[[]
LIKE 中有 3 个有意义的字符:
% _ [
。您不需要转义尾括号。为了完整性:
You need to replace the leading bracket by
[[]
There are 3 characters with meaning in LIKE:
% _ [
. You don't need to escape the trailing bracket.For completeness:
为什么在搜索参数为空的情况下将模型与其自身进行比较?这很贵。
将 and 和 or 与 SQL Server 混合使用也不是很好的做法,因为它很昂贵,但可以这样做:
如果性能可能是一个问题,那么引入 if 可以有所帮助,但最终会重复您的选择
您可以,但它会让你的 dba 不高兴,在代码中构建 sql 并使用 sqlparameters,并且仅在该 var 不为空时添加 where
Why are you comparing the model with itself in case of null search parameter? This is expensive.
It is also not great practice to mix ands and ors with SQL Server as its expensive, but could be done:
if performance could be a prob, then introducing an if can help, but you do end up duplicating your select
You could, but it will upset your dba, build the sql up in code and use sqlparameters and only add the where when this var is not null