Random 64 Characters alphanumeric String

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  November 9, 2016 at 11:34 am

  #1911472

  Jeff, have you been thinking about this for the last 7 years?

  😎

• Jeff Moden

  SSC Guru

  Points: 1004323

  More actions

  November 9, 2016 at 4:36 pm

  #1911514

  Eirikur Eiriksson (11/9/2016)

  ---

  Jeff, have you been thinking about this for the last 7 years?

  😎

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Mike Hinds

  SSCarpal Tunnel

  Points: 4258

  More actions

  November 10, 2016 at 5:18 am

  #1911612

  Really nice solution, Lynn!

  Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 10, 2017 at 2:53 am

  #1927705

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  February 10, 2017 at 3:16 am

  #1927708

  For fun, a variation of Chris's solution
  😎

  
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