CODEWITHSUNDEEP
sql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8705

PATINDEX and CHARINDEX returning different answers SQL Server 2008

I have a single table for which I wish to find all the instances where 0 is the last character in a string field. I tried this by using two methods and they each returned different results.

This first query seemed to actually return the correct answer

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

this one does not catch all of 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 the set of data that CHARINDEX picked up that PATINDEX did not. I even did

update icd
set ICD=RTRIM(icd)

Why would PATINDEX indiscriminately leave out some rows?

Upvotes: 4

Views: 4985

Answers (3)

Diego
Diego

Reputation: 36176

hard to say withou some data but I thiknk is that because you telling the CHARINDEX to start at the last position of the string, so it will only consider the last char (which seems what you want to do) but PATINDEX is lloking into all the string.

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

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

Upvotes: 1

DJ.
DJ.

Reputation: 16257

Wouldn't PATINDEX return the 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??

Upvotes: 2

JBrooks
JBrooks

Reputation: 10013

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

Upvotes: 4

Related Questions