Random 64 Characters alphanumeric String

Question

Post reply

Random 64 Characters alphanumeric String

AFIFM

SSC Eights!

Points: 902
More actions
March 11, 2009 at 7:32 pm

#206872

I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

select

Random_String =

substring(x,(abs(checksum(newid()))%36)+1,1)+

/* and so on for as many characters as needed */

substring(x,(abs(checksum(newid()))%36)+1,1)

from

(select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

Results:

Random_String

-------------

T7TAR

Brigadur SSCrazy Points: 2097 More actions · Answer 2

How about using the builtin newid() funcion like:

DECLARE @rand AS char(64)

SELECT @rand=replace(cast(newid() AS varchar(36))+cast(newid() AS varchar(36)),'-','')

SELECT @rand

AFIFM SSC Eights! Points: 902 More actions · Answer 3

Thank you Istvan, the newid() function works great. The sample code by Michael also does the job nicely except that it will only generates 5 characters string and I need 64 chracters.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

AFIFM (3/12/2009)
...The sample code by Michael also does the job nicely except that it will only generates 5 characters string and I need 64 chracters.

I just assumed you would be able to figure out the simple changes needed to get 64 characters, since it is only necessary for you to do cut and paste.

You should be aware that the other solution posted will only return the following characters, and not letters G through Z:

0123456789ABCDEF

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

AFIFM (3/11/2009)
I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.

What are you doing with this random non-unique string?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Here is another solution:

with

a1 as (select 1 as N union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select

row_number() over (order by N) as N

from

a4)

, cteRandomString (

RandomString

) as (

select top (64)

substring(x,(abs(checksum(newid()))%36)+1,1)

from

Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

)

select

replace((select

',' + RandomString

from

cteRandomString

for xml path ('')),',','');

GSquared SSC Guru Points: 260824 More actions · Answer 7

I think I'd be more inclined to use the one that stacks newids together than the one that actually picks more random character strings. Less likely to end up with obscene/offensive strings. Inevitably, this random string will end up being seen by someone who insists on having a problem with the dev who designed it, if it accidentally contains such. Murphy's Law and all that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

AFIFM SSC Eights! Points: 902 More actions · Answer 8

jcrawf02 (3/12/2009)
AFIFM (3/11/2009)
I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.
What are you doing with this random non-unique string?

I have an application that takes the user's password and converts it into one-way hash SHA256 64 characters alphanumeric field. We needed to create a second field to use as token (handshake) between two separate applications and I thought I would just use the same thing. If I had to do it from scratch I would have used the newid() function but these are legacy data and it will be cumbersome to change.

Lowell SSC Guru Points: 323504 More actions · Answer 9

ahh Lynn I like that code;

I parameterized the TOP (64) with @top, and can use that to generate any length whether 64 here, ro whatever i might need.

very nice!

Thanks for the addition to my snippets collection!

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!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

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?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

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;

Lowell SSC Guru Points: 323504 More actions · Answer 12

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!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

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?

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 14

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)