I hope I'm missing something obvious here ...
I need to select records based upon 5-digit zips from a subquery, so I could use left(cZip,5) in (...), but this isn't going to optimize without a special index. Is there a way to make the comparison non-exact? Some SET ANSII OFF (ala Visual FoxPro) type setting?
Is the best option to build a long "WHERE cZip Like '99999%' OR czip Like '99998%' OR ..." or maybe build some complex pattern string for a single LIKE comparison.
You can minimize the LIKE with a list or interval of characters
[listCharacters] or [startCharacter-EndCharacter] or not [^...], read
LIKE on BOL.
WHERE cZip Like '9999[7-9]%' OR ...
An other possibility is that one to define an special index for LEFT(cZip, 5).
In order to make this, you define a computed field cZip5 = LEFT(cZip, 5)
and creates an index for cZip5.
Of course, the WHERE becomes:
WHERE cZip5 IN (...)
Everyone has advantages and disadvantages.