how to compare string in SQL without using LIKE

Question

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 :

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

Please, clarify your question. If you need exact match - then why do you use like operator instead of equality? — Andrey Korneyev
– Andrey Korneyev, Commented Oct 19, 2015 at 11:50
I'm not positive but you might want to take a look at PATINDEX or CHARINDEX, although I believe they only return an INT, being the location your pattern starts. — MCP_infiltrator
– MCP_infiltrator, Commented Oct 19, 2015 at 11:51
yes I need exact match.. . I used equality but it showed no records.. but there are records — Arrow
– Arrow, Commented Oct 19, 2015 at 11:52
@gautamshetty Can you post your query you tried using an equality match. Along with samples of what the actual codes look like. If you include sample data and an expected outcome. It's much easier to help with your issue. — JBond
– JBond, Commented Oct 19, 2015 at 11:54

Gordon Linoff · Accepted Answer · 2015-10-20 00:38:16Z

2

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.

edited Oct 20, 2015 at 0:38

answered Oct 19, 2015 at 12:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3291673 · Accepted Answer · 2015-10-19 12:29:54Z

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

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

answered Oct 19, 2015 at 12:29

user3291673

Collectives™ on Stack Overflow

how to compare string in SQL without using LIKE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related