InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:50:05 GMT20http://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxhere's one way. Not necessarily the most efficient, but it's not bad.[code]drop table #mattdeclare @rows_needed intdeclare @length_needed intselect @rows_needed=1000, @length_needed=64select top(@rows_needed*@length_needed) identity(int,1,1) RN,cast(N as int) N,0 as batchcol,'' as randchar into #matt from tallyorder by newid()update #mattset 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[/code]Thu, 12 Mar 2009 12:47:58 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxgreat 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....Thu, 12 Mar 2009 10:02:13 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxAnother variation from Lynn's version:[code];witha1 as (select 1 as N union all select 1 union all select 1 union all select 1 union allselect 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 RandomStringfrom ( 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;[/code]Thu, 12 Mar 2009 09:18:56 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspx[code]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+randcharfrom MyCTE2select @n[/code]Thu, 12 Mar 2009 08:42:26 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxahh 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!Thu, 12 Mar 2009 08:38:38 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspx[quote][b]jcrawf02 (3/12/2009)[/b][hr][quote][b]AFIFM (3/11/2009)[/b][hr]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.[/quote]What are you doing with this random non-unique string?[/quote]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.Thu, 12 Mar 2009 08:30:04 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxI 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.Thu, 12 Mar 2009 08:19:37 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxHere is another solution:[code]witha1 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)selectreplace((select ',' + RandomStringfrom cteRandomStringfor xml path ('')),',','');[/code]Thu, 12 Mar 2009 08:17:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspx[quote][b]AFIFM (3/11/2009)[/b][hr]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.[/quote]What are you doing with this random non-unique string?Thu, 12 Mar 2009 07:44:07 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspx[quote][b]AFIFM (3/12/2009)[/b][hr]...The sample code by Michael also does the job nicely except that it will only generates 5 characters string and I need 64 chracters.[/quote]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:0123456789ABCDEFThu, 12 Mar 2009 07:29:41 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxThank 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.Thu, 12 Mar 2009 05:27:06 GMTAFIFMhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxHow 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 @randThu, 12 Mar 2009 04:59:59 GMTBrigadurhttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspx[code]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[/code]Wed, 11 Mar 2009 22:42:45 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic673876-1291-1.aspxI 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.Wed, 11 Mar 2009 19:32:19 GMTAFIFM