SQL---LIKE中的‘[’或‘]’

来源：互联网发布：淘宝清杂款衣服店铺编辑：程序博客网时间：2024/06/15 08:46

最近这两天在改公司项目中的BUG，遇到一个问题很奇怪。如下：

<span style="font-size:14px;">SELECT TOP 5 [Name] FROM [tblUt] ORDER BY [Created] DESC</span>

<span style="font-size:14px;">SELECT TOP 5 [Name] FROM [tblUt] <span style="color:#FF0000;"><strong>WHERE [Name]</strong></span> <span style="color:#FF0000;"><strong>LIKE '[</strong>%<strong>'</strong> </span> ORDER BY [Created] DESC（结果为空）<img src="http://img.blog.csdn.net/20150130104035062?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzAyODExMA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" /></span>

<span style="font-size:14px;">SELECT TOP 5 [Name] FROM [tblUt] <strong><span style="color:#FF0000;">WHERE [Name]</span></strong> <span style="color:#FF0000;"><strong>LIKE '[[]</strong>%<strong>'</strong></span>  ORDER BY [Created] DESC<img src="http://img.blog.csdn.net/20150130104339888?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzAyODExMA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" /></span><pre name="code" class="sql"><span style="font-size:14px;">SELECT TOP 5 [Name] FROM [tblUt] <span style="color:#FF0000;"><strong>WHERE [Name]</strong></span> <span style="color:#FF0000;"><strong>LIKE '</strong>%<strong>[]]'</strong></span>  ORDER BY [Created] DESC（结果为空）</span>

<span style="font-size:14px;">SELECT TOP 5 [Name] FROM [tblUt] <span style="color:#FF0000;"><strong>WHERE [Name]</strong></span> <span style="color:#FF0000;"><strong>LIKE '</strong>%<strong>]'</strong></span>  ORDER BY [Created] DESC</span>

查了些资料了解到‘[’、‘]’在SQL的LIKE中属于特殊字符，需要特别对待一下。所以才会出现上述情况。

除了‘[’、‘]’之外，在LIKE中还有一些特殊的字符以及其处理方法，如下：

</pre><img src="http://img.blog.csdn.net/20150130105408335?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzAyODExMA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" /><div align="center"><pre name="code" class="sql">SET @strWHERE=@strWHERE+' LIKE '''+'[[]'+@strSearchString1+'[]'+@strSearchString2+'%'''

如果在拼SQL语句的时候出现类似于上述情况的话，只需要将第一个’[‘替换为’[[]‘之后的’[‘替换为‘[]’即可。不需要写成如下的语句：

SET @strWHERE=@strWHERE+' LIKE '''+'[[]'+@strSearchString1+'[[]'+@strSearchString2+'%'''

这样的话结果中会多出一个‘[’。

如果遇到这种情况一定要谨记。

0 0