Creating Test Wildcards with a Common Table Expression in SQL Server 2005
I needed to create a SQL Server table of test wildcard strings ranging from ‘AA%’ through to ‘ZZ%’. This seemed to be a prime candidate for using recursion with a Common Table Expression (CTE). After a little playing around I came up with the following:
with wildcards as
(select char(65) + char(65) + '%' as wildcard, 65 as num1, 65 as num2
union all
select char(
case when num2 = 90 then num1 + 1
else num1 end)
+ char(
case when num2 = 90 then 65
else num2 + 1 end)
+ '%',
case when num2 = 90 then num1 + 1
else num1 end,
case when num2 = 90 then 65
else num2 + 1 end
from wildcards where num1 < 91
and (num1 <> 90 or num2 <> 90)
)
Obviously, to actually put the results somewhere useful, I had an INSERT statement which selected from the Wildcards CTE:
insert into TestFilter (Filter)
select wildcard from wildcards option(MaxRecursion 676)
Selecting the num1 and num2 values from the Wildcards CTE shows you what is going on:
select * from wildcards option(MaxRecursion 676)