Wednesday, August 12, 2009 6:53 AM 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: ,

Comments

# re: Using CTE (Common Table Expression) and Views to generate data

Wednesday, August 12, 2009 11:09 AM by Madhivanan

# Using CTE (Common Table Expression) and Views to generate data

Wednesday, August 12, 2009 7:37 PM by SqlServerKudos

Kudos for a great Sql Server article - Trackback from SqlServerKudos