Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Random 64 Characters alphanumeric String Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 7,064, Visits: 15,278
declare @n varchar(64)

set @n='';

;with MyCTE1 as (
select N, newid() as rid from tally),
MyCte2 as (
select top(64) char(n%84+32) randchar from MyCTE1 order by rid)
select @n=@n+randchar
from MyCTE2

select @n




----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #674311
Posted Thursday, March 12, 2009 9:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 3,105, Visits: 11,498
Another variation from Lynn's version:
;with
a1 as (
select 1 as N union all select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1
),
cteRandomString as (
select
substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',(abs(checksum(newid()))%36)+1,1)
as RandomString
from
( select 1 as N from a1 as a cross join a1 as b ) a
)
select
convert(varchar(64),replace((
select
','+RandomString
from
cteRandomString
for xml path (''))
,',','')) as RandomString;

Post #674351
Posted Thursday, March 12, 2009 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
great code examples; what if i wanted to generate X number strings, for arguments sake lets say 10...i can't seem to visualize how to generate multiple records though.

rub my nose in it and get the newspaper....


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #674409
Posted Thursday, March 12, 2009 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 7,064, Visits: 15,278
here's one way. Not necessarily the most efficient, but it's not bad.
drop table #matt

declare @rows_needed int
declare @length_needed int

select @rows_needed=1000,
@length_needed=64

select top(@rows_needed*@length_needed)
identity(int,1,1) RN
,cast(N as int) N
,0 as batchcol
,'' as randchar
into #matt
from tally
order by newid()

update #matt
set batchcol = cast(rn/@length_needed as int),
randchar = char(n%52+65)

create index m on #matt(batchcol) include(randchar)

select distinct batchcol,
(select
randchar as 'text()'
from
#matt m_in
where m_in.batchcol=m_out.batchcol
for xml path ('')) from #matt m_out



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #674590
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse