0

I'm trying to create a stored procedure to randomly generate two unique alphanumeric characters from the following character set on SQL Server 2016: ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789 (35-character set). The random values will then be stored on a custom column on the AspNetUsers table.

Currently, only 132 unique 2-character values are stored on the AspNetUsers table, while there can be a total of 1,225 permutations. This is based on the permutations formula: n! / (n - r)! or 35! / (35 - 2)! = 1225.

The following code, based on this example works sometimes, while other times, it becomes an infinite loop. The outer WHILE ensures the value is unique on the table where it will be stored.

DECLARE @length int, @charpool varchar(256), @poollength int, 
    @loopcount int, @randomstring varchar(100)
SET @Length = 2

-- define allowable character explicitly - easy to read this way an easy to 
SET @CharPool = 'ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789'
SET @PoolLength = Len(@CharPool)

SET @LoopCount = 0
SET @RandomString = ''

WHILE (SELECT count(*) FROM AspNetUsers anu WHERE anu.CustomId = @randomstring) > 0 BEGIN
    WHILE (@LoopCount < @Length) BEGIN
        SELECT @RandomString = @RandomString + 
            SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1)
        SELECT @LoopCount = @LoopCount + 1
    END
END
PRINT @randomstring
Improve this question
5
  • 1
    Well, there's no re-initiation of @randomstring and @loopcount after the first while, so once you set a random value that exist, you continue to loop until forever. Btw you don't need to loop to make this work, unless you use it to keep the server room warm Commented Nov 12, 2024 at 12:46
  • Thanks, @siggemannen. Do you mind putting that into an answer with corrected code? I just tested and it works :) Commented Nov 12, 2024 at 12:51
  • And how would you do this without the loop? Commented Nov 12, 2024 at 13:02
  • I added a larger example as an answer Commented Nov 12, 2024 at 13:05
  • @MartinSmith, it's version 2016 Commented Nov 12, 2024 at 13:21

1 Answer 1

Reset to default
3

You can do something like this:

-- Seed existing
DROP TABLE IF EXISTS #aspusers 
CREATE TABLE #aspusers (id int IDENTITY, somerandomValue varchar(2) UNIQUE)

DECLARE @length int, @charpool varchar(256), @poollength int, 
    @loopcount int, @randomstring varchar(100)
SET @Length = 2

-- define allowable character explicitly - easy to read this way an easy to 
SET @CharPool = 'ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789'

;WITH data AS (
    SELECT  TOP(len(@charpool)) row_number() OVER(ORDER BY @@spid) AS rank
        FROM    sys.objects so
    )
INSERT INTO #aspusers (somerandomValue)
SELECT  TOP 132 chrs
FROM    (
    SELECT  substring(@charpool, d.rank, 1) + substring(@charpool, d2.rank, 1) AS chrs
    FROM    data d
    CROSS JOIN data d2
    ) x
ORDER BY NEWID()

-- Generate new random value
;WITH data AS (
    SELECT  TOP(len(@charpool)) row_number() OVER(ORDER BY @@spid) AS rank
        FROM    sys.objects so
    )
SELECT  TOP 1 @randomstring = x.chrs
FROM    data d
CROSS JOIN data d2
CROSS apply (
        SELECT  substring(@charpool, d.rank, 1) + substring(@charpool, d2.rank, 1) AS chrs
    ) x
WHERE   NOT EXISTS(
        SELECT  1
        FROM    #aspusers asp
        WHERE   asp.somerandomValue = x.chrs
    )
ORDER BY NEWID()

-- Output
SELECT  @randomstring, *
FROM    #aspusers

The main idea is to generate a list of numbers between 1 and len(charpool) which is then used to create the combination of characters using SUBSTRING.

To generate a new random value, one just have to add a WHERE NOT EXISTS which checks the original table.

The whole sys.objects thing can be replaced with GENERATE_SERIES if you're on sufficiently new sql server version.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks, @siggermannen. What does the INSERT INTO #aspusers (somerandomValue) SELECT TOP 132 chrs represent?
It was the replicate your situation with original random numbers already generated
No, in your real code you would check against your "real" table of course. These questions make me suspect you didn't understand my answer though :( Maybe i should delete it?
@alex the first code block is just initialising a representation of your AspNetUsers table, with random data, as you didn't give a minimal reproducible example
As there are only 1225 possible values, wouldn't it be simpler to initialise them all just once? Store them in a dimension table?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.