A) The column is a Varchar(50)

B) AvgLen = 10 

    MaxLen = 32 

    MinLen  = 2 

    Count   = 82325

C) Because of the industry that I work in, I can not provide

    A) Sample Data

    B) Table Layouts

    C) ER Diagrams

However I can tell you that @PassedPartNumber would be part or all of a part number

So @PassedPartNumber could be '269' or '123' or '24d121' ... ect

Thank you again for your interest in this. Something else I found to be interesting..

No matter how large the dataset the results are the same, PATINDEX

works faster then LIKE in ALL of the selects I have tried.

Will