Just help out may colleague to write a query where we needed to filter out all data that contains characters and because a column is defined as VARCHAR we CAST it to INTEGER in order to implement range searching. Please see simplified demo script.
CREATE TABLE #t (c varchar(50))
INSERT INTO #t VALUES ('122')
INSERT INTO #t VALUES ('4545')
INSERT INTO #t VALUES ('4545/454')
INSERT INTO #t VALUES ('4899')
----Failed
SELECT * FROM
(
SELECT c FROM #t WHERE c NOT LIKE '%[/]%'
) AS d WHERE CAST(c AS INT)>10
----Succeed
SELECT * FROM #t
WHERE CASE WHEN c LIKE '%[^0-9]%' THEN 0
WHEN CAST(c AS int) BETWEEN 1 AND 1000 THEN 1
ELSE 0 END = 1
The answer why the first attemp is failed we found looking at execution plan.
Predicate
CONVERT(int,[tempdb].[dbo].[#t].[c],0)>(10) AND NOT [tempdb].[dbo].[#t].[c] like '%[/]%'
Thinking that we filter out all "bad" rows and can CAST the rest is wrong because as we see above predicate is applied for the whole table.
As opposite the second query we used CASE expression to filter out "bad" rows CASE...=1 we see that SQL Server really filters out "bad" rows and now CAST is working.
Predicate
CASE WHEN [tempdb].[dbo].[#t].[c] like '%[^0-9]%' THEN (0) ELSE CASE WHEN CONVERT(int,[tempdb].[dbo].[#t].[c],0)>=(1) AND CONVERT(int,[tempdb].[dbo].[#t].[c],0)<=(1000) THEN (1) ELSE (0) END END=(1)
Sunday, November 6, 2011
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment