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
Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46728 Visits: 19270

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
Michael Valentine Jones
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23867 Visits: 11923
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
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123558 Visits: 41464
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)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46728 Visits: 19270
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
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340421 Visits: 42644
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63482 Visits: 19823
Jeff, have you been thinking about this for the last 7 years?
Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340421 Visits: 42644
Eirikur Eiriksson (11/9/2016)
Jeff, have you been thinking about this for the last 7 years?
Cool


Nah... just ran into the post. Just went through this in the company I work for, though. Base 26 and Base 36 numberings systems have more bad words than I do and I was in the U.S. Navy for more than 8 years. ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike Hinds
Mike Hinds
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1844 Visits: 1145
Really nice solution, Lynn!

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65906 Visits: 20214
Dunno how I landed here, but...here's how I'd do it in current versions (this doesn't work with '2005):

SELECT CONVERT(CHAR(64),HASHBYTES('SHA2_512',CAST(NEWID() AS VARCHAR(36))),2)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63482 Visits: 19823
For fun, a variation of Chris's solution
Cool


DECLARE @BINID VARBINARY(16) = NEWID();
;WITH BINARY_STUFF(DHASH,BINDATA,BINID)
AS
(
SELECT CONVERT(CHAR(64),HASHBYTES('SHA2_512',CONVERT(VARCHAR(36),@BINID,0)),2),CONVERT(VARCHAR(36),@BINID,0),@BINID UNION ALL
SELECT CONVERT(CHAR(64),HASHBYTES('SHA2_512',CONVERT(VARCHAR(36),@BINID,1)),2),CONVERT(VARCHAR(36),@BINID,1),@BINID UNION ALL
SELECT CONVERT(CHAR(64),HASHBYTES('SHA2_512',CONVERT(VARCHAR(36),@BINID,2)),2),CONVERT(VARCHAR(36),@BINID,2),@BINID
)
SELECT
BS.DHASH
,BS.BINDATA
,BS.BINID
FROM BINARY_STUFF BS;



Sample output

DHASH BINDATA BINID
---------------------------------------------------------------- ------------------------------------ ----------------------------------
AF9BD5FDA0A507E82823FBFADEC8F9991C3E47E7FC927236653D1A3D9F3DC846 ‰ƒÎâ‰zÝE˜ƒtˆd¶üÍ 0x8983CEE2897ADD459883748864B6FCCD
8AF91B54BB28E29AA2E0EC9897CD93F20C6FF42BB249F0B34915085DF773516C 0x8983CEE2897ADD459883748864B6FCCD 0x8983CEE2897ADD459883748864B6FCCD
25F9F7297DA8123DCFDB9A81D5AFCD0EF46217A4FA787D42E6AC32F744397BEE 8983CEE2897ADD459883748864B6FCCD 0x8983CEE2897ADD459883748864B6FCCD




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