March 11, 2009 at 7:32 pm
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.
March 11, 2009 at 10:42 pm
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
March 12, 2009 at 5:00 am
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
March 12, 2009 at 5:27 am
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.
March 12, 2009 at 7:29 am
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
March 12, 2009 at 7:44 am
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."
March 12, 2009 at 8:17 am
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 ('')),',','');
March 12, 2009 at 8:19 am
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
March 12, 2009 at 8:30 am
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.
March 12, 2009 at 8:38 am
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
March 12, 2009 at 8:42 am
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?
March 12, 2009 at 9:18 am
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;
March 12, 2009 at 10:02 am
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
March 12, 2009 at 12:47 pm
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?
November 8, 2016 at 11:01 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply