Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 @ 9:09 PM
Points: 7,507, Visits: 17,958
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: Saturday, October 24, 2015 2:31 AM
Points: 3,158, Visits: 11,771
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: Today @ 9:23 AM
Points: 14,551, Visits: 38,425
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #674409
Posted Thursday, March 12, 2009 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 7,507, Visits: 17,958
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
Posted Tuesday, November 8, 2016 11:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 42,077, Visits: 39,463
I know this is a really old thread but, to avoid building a random foul word generator, there's an easy method to do this.

SELECT REPLACE(CONVERT(CHAR(36),NEWID())+CONVERT(CHAR(36),NEWID()),'-','')


p.s. This does sound like an interview question.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1833087
Posted Wednesday, November 9, 2016 11:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 6,564, Visits: 17,270
Jeff, have you been thinking about this for the last 7 years?
Post #1833286
Posted Wednesday, November 9, 2016 4:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 42,077, Visits: 39,463
Eirikur Eiriksson (11/9/2016)
Jeff, have you been thinking about this for the last 7 years?


Nah... just ran into the post. Just went through this in the company I work for, though. Base 26 and Base 36 numberings systems have more bad words than I do and I was in the U.S. Navy for more than 8 years.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1833339
Posted Thursday, November 10, 2016 5:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 5, 2016 5:56 AM
Points: 777, Visits: 1,063
Really nice solution, Lynn!

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Post #1833479
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse