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 Wednesday, March 11, 2009 7:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 8:25 AM
Points: 24, 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.
Post #673876
Posted Wednesday, March 11, 2009 10:42 PM
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 @ 12:18 AM
Points: 3,113, Visits: 11,541
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

Post #673930
Posted Thursday, March 12, 2009 4:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
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
Post #674072
Posted Thursday, March 12, 2009 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 8:25 AM
Points: 24, 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.
Post #674083
Posted Thursday, March 12, 2009 7:29 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 @ 12:18 AM
Points: 3,113, Visits: 11,541
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
Post #674210
Posted Thursday, March 12, 2009 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
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."
Post #674225
Posted Thursday, March 12, 2009 8:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 20,862, Visits: 32,899
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 ('')),',','');




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)
Post #674270
Posted Thursday, March 12, 2009 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #674272
Posted Thursday, March 12, 2009 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 8:25 AM
Points: 24, 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.
Post #674291
Posted Thursday, March 12, 2009 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 12,963, Visits: 32,511
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

--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 #674303
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse