Select Distinct RCD.Variable4 
FROM Route_Call_Detail RCD 
WHERE RCD.[DateTime] > '12/4/10' AND RCD.[DateTime] < '12/5/10' 
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
--AND CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999 

SELECT * FROM (
Select 
  Distinct RCD.Variable4 
FROM Route_Call_Detail RCD 
WHERE RCD.[DateTime] > '12/4/10' 
  AND RCD.[DateTime] < '12/5/10' 
  AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
) MyAlias
WHERE CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999 

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Select 
  Distinct RCD.Variable4 
FROM Route_Call_Detail RCD 
WHERE RCD.[DateTime] > '12/4/10' 
  AND RCD.[DateTime] < '12/5/10' 
  AND CASE WHEN SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' THEN CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) ELSE NULL END BETWEEN BETWEEN 5011 AND 7999 
)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ken.trock (12/6/2010)

---

Hello. I pull data from a SQL 2000 environment via SSIS into a 2008 DB of ours.

Select Distinct RCD.Variable4 
FROM Route_Call_Detail RCD 
WHERE RCD.[DateTime] > '12/4/10' AND RCD.[DateTime] < '12/5/10' 
AND SUBSTRING(RCD.Variable4, 1, 6) NOT LIKE '%[^0-9]%' --Thanks to Jeff Moden for this
--AND CONVERT(INT, SUBSTRING(RCD.Variable4, 1, 6)) BETWEEN 5011 AND 7999 

This works fine, purely numeric values returned for Variable4 1st 6 chars. Uncomment that last line and I get this error:

Syntax error converting the varchar value 'AZ' to a column of data type int.

There are rows where Variable4 = 'AZ' but I thought those would be short circuited by the statement above. It works in test Anyhow, I tried putting this in a case statement like some people have offered but no luck. Keep in mind the query runs in 2000 before getting over to us (we're fortunately not the keepers of the 2000 data).

Thanks,
Ken

AND RCD.Variable4 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'