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 :crazy: 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
Ken,
A LIKE that starts with a '%' won't be able to utilize an index on the column, which may make a noticeable impact. Since you want the first six characters to be digits, you might want to change it to:
AND RCD.Variable4 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'
which is SARGable and thus will utilize an index.
This might also fix the issue you were having... if sql is already doing a scan on the table because of this, it would just go ahead and compare the other column at the same time. You would probably still need to put it into a CTE or sub-query as Lowell showed you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes