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.