PATINDEX and CHARINDEX return different SQL Server 2008 responses

Question

I have one table for which I want to find all instances, where 0 is the last character in the row field. I tried this using two methods, and each of them returned different results.

This first query seemed to really return the correct answer

 select * from icd where CHARINDEX('0',icd,LEN(icd)) =LEN(icd) 

this one will not catch all the answers

 select * from icd where PATINDEX('%0%',icd) = LEN(icd) 

using

 select t.ICD as theCharIndex,x.ICD as thePatIndex from ( select * from icd where CHARINDEX('0',icd,LEN(icd)) =LEN(icd) ) t left join ( select * from icd where PATINDEX('%0%',icd) = LEN(icd) ) x on x.ICD=t.ICD where x.ICD is null 

I found a dataset that CHARINDEX took that PATINDEX did not. I even did

 update icd set ICD=RTRIM(icd) 

Why PATINDEX leave several lines indiscriminately?

Answer 1

Because you have 0 somewhere to the end. Change PATINDEX('%0%',icd) to PATINDEX('%0',icd)

Answer 2

Would not return PATINDEX FIRST "0" in the string, and therefore, if the string had more than one zero, PATINDEX('%0%',icd) = LEN(icd) , would be false even if there was another zero at the end?

Answer 3

it's hard to say with some data, but I know because you tell CHARINDEX to start at the last position of the line, so it will only consider the last char (which seems to be what you want to do), but PATINDEX is locked to the entire line.

FYI, I think it would be better to achieve what you want with the SUBSTRING function

 where SUBSTRING(icd, len(icd),1)