Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 45 , trackbacks - 0

Creating SQL Wildcards with Recursion and a Common Table Expression in SQL Server 2005

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)
 

Print | posted on Tuesday, October 28, 2008 3:21 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Creating SQL Wildcards with Recursion and a Common Table Expression in SQL Server 2005

This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another nice post with wonderful explanation on wildcards in sql server over internet.
please check out this link...

http://mindstick.com/Articles/8009b2bd-a5ec-427a-ae50-003a850a7e0e/?SQL%20Wildcards

Thanks

> Blockquote
12/26/2011 8:27 AM | Ajay Singh
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: