SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Random 64 Characters alphanumeric String


Random 64 Characters alphanumeric String

Author
Message
AFIFM
AFIFM
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 54
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.
Michael Valentine Jones
Michael Valentine Jones
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30762 Visits: 11933
select
Random_String =
substring(x,(abs(checksum(newid()))%36)+1,1)+
substring(x,(abs(checksum(newid()))%36)+1,1)+
substring(x,(abs(checksum(newid()))%36)+1,1)+
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
Brigadur
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 27695
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
AFIFM
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 54
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
Michael Valentine Jones
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30762 Visits: 11933
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
jcrawf02
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9252 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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
Lynn Pettis
SSC Guru
SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)SSC Guru (191K reputation)

Group: General Forum Members
Points: 191586 Visits: 39828
Here is another solution:


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 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 ('')),',','');



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GSquared
GSquared
SSC Guru
SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)

Group: General Forum Members
Points: 120275 Visits: 9730
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
AFIFM
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 54
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
Lowell
SSC Guru
SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)

Group: General Forum Members
Points: 157465 Visits: 41543
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search