如何在不使用LIKE的情况下比较SQL中的字符串

I am using SQL query shown below to compare AMCcode. But if I compare the AMCcode '1' using LIKE operator it will compare all the entries with AMCcode 1, 10,11,12,13. .. 19, 21,31.... etc. But I want to match the AMCcode only with 1. Please suggest how can I do it. The code is given below :

我正在使用下面显示的SQL查询来比较AMCcode。但是，如果我使用LIKE运算符比较AMCcode'1'，它将比较所有条目与AMCcode 1,10,11,12,13。 .. 19,21,31 ......等。但我想将AMCcode仅与1匹配。请建议我该怎么做。代码如下：

ISNULL(CONVERT(VARCHAR(10),PM.PA_AMCCode), '') like          
  (        
   CASE         
    WHEN @AMCCode IS NULL THEN '%'        
    ELSE '%'+@AMCCode+ '%'        
   END        
  )

This is part of the code where I need to replace the LIKE operator with any other operator which will give the AMCcode with 1 when I want to search AMCcode of 1, not all 10,11,12..... Please help

这是代码的一部分，我需要用任何其他运算符替换LIKE运算符，当我想搜索AMCcode为1时，将给AMCcode 1，而不是所有10,11,12 .....请帮忙

2 个解决方案

#1

I think you are looking for something like this:

我想你正在寻找这样的东西：

where ',' + cast(PM.PA_AMCCode as varchar(255)) + ',' like '%,' + @AMCCodes + ',%'

This includes the delimiters in the comparison.

这包括比较中的分隔符。

Note that a better method is to split the string and use a join, something like this:

请注意，更好的方法是拆分字符串并使用连接，如下所示：

select t.*
from t cross apply
     (select cast(code as int) as code
      from dbo.split(@AMCCodes, ',') s(code)
     ) s
where t.AMCCode = s.code;

This is better because under some circumstances, this version can make use of an index on AMCCode.

这样更好，因为在某些情况下，此版本可以使用AMCCode上的索引。

#2

If you want to exactly match the value, you don't need to use '%' in your query. You can just use like as below

如果要与值完全匹配，则无需在查询中使用“％”。你可以像下面这样使用

ISNULL(CONVERT(VARCHAR(10),PM.PA_AMCCode), '') like          
  (        
   CASE         
    WHEN @AMCCode IS NULL THEN '%'        
    ELSE @AMCCode        
   END        
  )

Possibly you can remove case statement and query like

可能你可以删除case语句和查询

ISNULL(CONVERT(VARCHAR(10),PM.PA_AMCCode), '') like @AMCCode

#1