Real Unique Random AlphaNumeric

Jassim Al Rahma 1,536 Reputation points
2022-04-06T19:48:24.767+00:00

Hi,

In SQL, how can I create a real random 256 alpha-numeric string without having to kill my server with looping and looping?

Thanks,
Jassim

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    2022-04-06T21:45:43.783+00:00

    This gives you a 344 character long Base64 string. It is not entirely alphanumeric, but it is random.

    DECLARE @x varchar(4000)
    SELECT @x = (SELECT crypt_gen_random(256) AS x
                 FOR XML PATH('root'), TYPE).value('/root[1]/x[1]', 'varchar(4000)')
    SELECT len(@x), @x
    
    1 person found this answer helpful.

  2. Naomi 7,361 Reputation points
    2022-04-06T20:20:18.023+00:00

    Try
    SELECT
    REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','') + REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','') + REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','')
    +REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','')
    +REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','') + REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','') + REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','')
    +REPLACE(CAST(NEWID() AS VARCHAR(256)),'-','') AS b

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points
    2022-04-07T03:17:07.327+00:00

    Hi @Jassim Al Rahma
    I usually use NEWID() to get unique string,like this:

    SELECT NEWID();  
      
    SELECT REPLACE(CONVERT(VARCHAR(64),NEWID()),'-','');--Remove '-'  
    

    In this way, you can get a random string with a length of 32. Since your request is for a length of 256, you need to connect 8 strings together.
    If you don't mind the presence of characters other than AlphaNumeric in the string, then you can refer to Erland's answer.

    Best regards,
    LiHong

    0 comments No comments