Hi Don,

If cZip is or you can make it of data type varchar that you don't need LEFT() function. You can test it with Nortwind DB.

SELECT Customers.* 
	FROM dbo.Customers 
	WHERE PostalCode IN (
		SELECT LEFT(PostalCode,5) 
			FROM dbo.Customers 
			WHERE LEN(PostalCode) = 5) 
--
SELECT Customers.* 
	FROM dbo.Customers 
	JOIN (
		SELECT DISTINCT LEFT(PostalCode,5) AS zip 
			FROM dbo.Customers 
			WHERE LEN(PostalCode) = 5) ziplist
		ON Customers.PostalCode = ziplist.zip

>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 VFP) 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.