How big is your partnumber data?
Would you mind telling me:
A) The data type of the column
B) the output from:
SELECT AVG(LEN(PartNumber)) AS AvgLen,
MAX(Len(PartNumber)) AS MaxLen,
MIN(Len(PartNumber)) AS MinLen,
COUNT(*) AS Count
and C) Could you post some sample data along with what kind of values you get as your @PassedPartNumber ?
I would really appreciate it. As I said, I have never been able to get PATINDEX return faster, so I'm very curious about when and why it does in your case. Thanks!