12 August 2009 06:53
tonyrogerson
Using CTE (Common Table Expression) and Views to generate data
I'm writing a new content area on http://sqlserverfaq.com/ to hold our LiveMeeting recordings and the other content we have and I wanted to create a short character/digit code that you can put on the end of the domain eg http://sqlserverfaq.com/FGRE2 that shorts the URL so you can go straight to the article - makes news letters and blog posts a lot neater.
Anyway, here is how you can use Views and CTE (Common Table BLOCKED EXPRESSION to accomplish generating A - Z and 0 - 9 and get a random selection of that for 'x' number of alphanumeric combinations...
In a real system you'd probably store them to inch that extra performance out, but - this is the fun and educational way of building the data....
create view vw_alpha_numerics
as
with chrs ( chr ) as (
select chr = ASCII( 'A' )
union all
select chr = chr + 1
from chrs
where chr < ASCII( 'Z' )
)
, nums ( chr ) as (
select chr = ASCII( '0' )
union all
select chr = chr + 1
from nums
where chr < ASCII( '9' )
)
select chr_code = chr, chr = CHAR( chr )
from chrs
union all
select chr_code = chr, chr = CHAR( chr )
from nums
go
with nums ( pos ) as (
select pos = 1
union all
select pos = pos + 1
from nums
where pos < 10
)
select top 10 va.chr
from nums n
cross join ( select chr
from vw_alpha_numerics
where chr not in ( '0', 'A', 'E', 'I', 'O', 'U' ) ) as va
order by NEWID()
And one of many ways of getting back the string...
declare @short_lookup_name varchar(10)
with nums ( pos ) as (
select pos = 1
union all
select pos = pos + 1
from nums
where pos < 10
)
select @short_lookup_name = (
select chr as [text()]
from (
select top 4 va.chr
from nums n
cross join ( select chr
from vw_alpha_numerics
where chr not in ( '0', 'A', 'E', 'I', 'O', 'U' ) ) as va
order by NEWID()
) as r
for xml path( '' ) )
print @short_lookup_name
Filed under: SQL Development, SQL Server FAQ