Generate random AlphaNumeric string using While Loop in SQL Server

nauna
 
on Mar 30, 2021 01:53 AM
740 Views

hello,

I am using this SP, it should have alpha numeric for each record it's generate but many times it does not generates alpha numeric all the time

CREATE PROCEDURE [dbo].[Random_Number_Generator]
    @maxNumbers INT   
AS
BEGIN
    BEGIN TRY
        DROP TABLE #rand_numbers
    END TRY
    BEGIN CATCH
        PRINT '##rand_numbers not available to drop'
    END CATCH
    
    CREATE TABLE #rand_numbers
    (
        rand_number VARCHAR(6)
    )
  
    DECLARE @loop_ctr INT = 0
    WHILE @loop_ctr < 5
    BEGIN
 
     
    DECLARE @NewId VARCHAR(255)
    SELECT @NewId = NEWID()
    SELECT CAST((ABS(CHECKSUM(@NewId))%10) AS VARCHAR(1)) +
    CHAR(ASCII('A')+(ABS(CHECKSUM(@NewId))%25)) + LEFT(@NewId,4)
        --DECLARE @chars NCHAR(62)
        --SET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
        --DECLARE @AlphaNumeric NCHAR(5)
        --SET @AlphaNumeric = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
        --+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
        --+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
        --+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
        --+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
  
        --INSERT #rand_numbers(rand_number)
        --SELECT @AlphaNumeric
    
        --SET @loop_ctr = @loop_ctr + 1
    END
  
    --SELECT rand_number FROM #rand_numbers
END

I want to keep it in a loop so i pass 100 it should generate 100 rows alpha numeric

kinldy advice

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 30, 2021 01:59 AM
on Mar 31, 2021 01:08 AM

Hi nauna,

Check with below query.

SQL

-- EXEC Random_Number_Generator 5
CREATE PROCEDURE [dbo].[Random_Number_Generator]
    @maxNumbers INT   
AS
BEGIN
    BEGIN TRY
		DROP TABLE #rand_numbers
    END TRY
    BEGIN CATCH
        PRINT '##rand_numbers not available to drop'
    END CATCH
    
    CREATE TABLE #rand_numbers (rand_number VARCHAR(6))
  
    DECLARE @loop_ctr INT = 0
    WHILE @loop_ctr < @maxNumbers
    BEGIN
	DECLARE @NewId VARCHAR(255), @AlphaNumeric NCHAR(10)
	SELECT @NewId = NEWID()
	SELECT @AlphaNumeric = CAST((ABS(CHECKSUM(@NewId))%10) AS VARCHAR(1)) +
	CHAR(ASCII('A')+(ABS(CHECKSUM(@NewId))%25)) + LEFT(@NewId,4)
  
	INSERT #rand_numbers(rand_number)
	SELECT @AlphaNumeric
    
	SET @loop_ctr = @loop_ctr + 1
    END
  
    SELECT rand_number FROM #rand_numbers
END

Output

rand_number

5UF63E

9OC255

9Y87DC

5ACE32

4YB80D