Generate random AlphaNumeric string using Stored Procedure in SQL Server

nauna
 
on Feb 28, 2021 10:34 PM
2207 Views

Hello

I have this sp which generate number random i want it should generate alpha numeric

Please advice.

create PROCEDURE [dbo].[Random_Number_Generator] 
    -- Add the parameters for the stored procedure here
    @min_integer int 
    ,@max_integer int 	
AS
BEGIN
    begin try
        drop table #rand_digits
    end try
    begin catch
        print '#rand_digits not available to drop'
    end catch
 
    create table #rand_digits
    (
        rand_digit tinyint
    )
 
    -- declare min and max random digit values
    -- and variables values to control loop count
    declare @loop_ctr int = 0,@max_loop_ctr int = 1000
 
    -- loop 1000 times 
    while @loop_ctr < @max_loop_ctr
    begin 
        -- generate a random digit from @min_integer through @max_integer
        -- and insert it into #rand_digits 
        insert #rand_digits(rand_digit)
        select floor(rand()*(@max_integer - @min_integer + 1) + @min_integer) 
        set @loop_ctr = @loop_ctr + 1 
    end
 
    -- count the number of each randomly computed digit
    -- and display the results
    select rand_digit, count(*) [frequency]
    from #rand_digits
    group by rand_digit
    --order by rand_digit
    order by newid()
END

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 01, 2021 04:16 AM

Hi nauna,

Refer below query.

SQL

--EXEC [dbo].[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 @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

Output

rand_number
dX9VC
E1Hl5
Rg2BC
ABD9U
VBQcO