Stored procedure to create distinct random alphanumeric values varying 5 and 10 characters long

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure to create distinct random alphanumeric values varying 5 and 10 characters long

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-26 : 09:40:07

Rick writes "SQL Server 2000, Windows NT
This is a tough one, hope you got the answer for the impossible question:
I am creating a stored procedure to insert records into a table. This procedure has to call another proc, and will have to pass some length value with low-end(5) and high-end(10)(the value generated should be between 5 and 10 chars long) and return 500 generated codes.

The called procedure will generate 500 codes that will be passed back to the calling program. The 500 records generated can vary in length from 5 char to 10 char long. Each character should be randomly generated having values of A-Z, 0-9 and
-. No codes to be duplicated.

So 1) pass from a stored procedure some length var that will say we are looking for a code to be brought back with low end length and high end length long.

2) stored procedure to generate 500 codes, each char randomly generated and no duplicates. Pass back codes to calling proc.
such as
56W309
WE49509
6T484395-0
29019
Thanks..."

JohnDeere
Posting Yak Master

191 Posts

Posted - 2002-07-26 : 15:33:11

Here is an attempt to solve the problem.
Unfortunately it loops to build the strings

CREATE procedure usp_RandomStrng
@low_end int = 5, @high_end int = 10
,@records_to_return int = 500
As

Set nocount on

Declare @random_number int
,@record_count int
,@randomizer int
,@length_cnt int
,@range_start int, @range_end int
,@record_Length int
,@i int
,@random_string varchar(10)
Set @range_start = 1
Set @range_end = 36
Set @randomizer = @range_end - @range_start + 1
Set @length_cnt = @high_end - @low_end + 1
set @random_string = ''

-- Table to hold random_strings
Create Table #return_data (random_length int, random_data varchar(10))

-- table of allowable characters
Create Table #Allowed_Characters (RecNo int identity(1,1),Display_value char(1))
insert into #Allowed_Characters (Display_value) values('0')
insert into #Allowed_Characters (Display_value) values( '1')
insert into #Allowed_Characters (Display_value) values( '2')
insert into #Allowed_Characters (Display_value) values( '3')
insert into #Allowed_Characters (Display_value) values( '4')
insert into #Allowed_Characters (Display_value) values( '5')
insert into #Allowed_Characters (Display_value) values( '6')
insert into #Allowed_Characters (Display_value) values( '7')
insert into #Allowed_Characters (Display_value) values( '8')
insert into #Allowed_Characters (Display_value) values( '9')
insert into #Allowed_Characters (Display_value) values( 'A')
insert into #Allowed_Characters (Display_value) values( 'B')
insert into #Allowed_Characters (Display_value) values( 'C')
insert into #Allowed_Characters (Display_value) values( 'D')
insert into #Allowed_Characters (Display_value) values( 'E')
insert into #Allowed_Characters (Display_value) values( 'F')
insert into #Allowed_Characters (Display_value) values( 'G')
insert into #Allowed_Characters (Display_value) values( 'H')
insert into #Allowed_Characters (Display_value) values( 'I')
insert into #Allowed_Characters (Display_value) values( 'J')
insert into #Allowed_Characters (Display_value) values( 'K')
insert into #Allowed_Characters (Display_value) values( 'L')
insert into #Allowed_Characters (Display_value) values( 'M')
insert into #Allowed_Characters (Display_value) values( 'N')
insert into #Allowed_Characters (Display_value) values( 'O')
insert into #Allowed_Characters (Display_value) values( 'P')
insert into #Allowed_Characters (Display_value) values( 'Q')
insert into #Allowed_Characters (Display_value) values( 'R')
insert into #Allowed_Characters (Display_value) values( 'S')
insert into #Allowed_Characters (Display_value) values( 'T')
insert into #Allowed_Characters (Display_value) values( 'U')
insert into #Allowed_Characters (Display_value) values( 'V')
insert into #Allowed_Characters (Display_value) values( 'W')
insert into #Allowed_Characters (Display_value) values( 'X')
insert into #Allowed_Characters (Display_value) values( 'Y')
insert into #Allowed_Characters (Display_value) values( 'Z')

Set @record_count=1
While @record_count < = @records_to_return
Begin
-- Generate random string length
Set @record_length = rand()* @length_cnt + @low_end
Set @i = 1
set @random_string = ''

-- Generate random string
While @i <= @record_length
Begin
Set @random_number = rand()* @randomizer + @range_start
Select @random_string = @random_string + cast(Display_Value as char(1))
From #Allowed_Characters
Where RecNo=@random_number
Set @i = @i + 1
End
-- Store finished string in table
Insert into #return_data (random_length,random_data) values(@record_length,@random_string)
Set @record_count = @record_count + 1
End

-- Return data to caller
Select random_length,random_data From #return_data

Set nocount off

Drop Table #Allowed_Characters
Drop Table #return_data

Go

Lance Harra

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-27 : 05:58:07

quote:

The called procedure will generate 500 codes that will be passed back to the calling program. The 500 records generated can vary in length from 5 char to 10 char long. Each character should be randomly generated having values of A-Z, 0-9 and
-. No codes to be duplicated.

Hmm... smells like homework to me. What the heck, submit this and see if your instructor gives you a positive or a negative mark


CREATE PROCEDURE RandomStrings
  @short int = 5, @long int = 10, @count int = 500,
  @chars varchar(8000) = '0123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ'
AS
DECLARE @crlf char(2), @i int, @j int
SET @crlf = CHAR(13) + CHAR(10)
SET @i = @long

DECLARE @s1 varchar(8000), @s2 varchar(8000)
DECLARE @s3 varchar(8000), @s4 varchar(8000)
SET @s1 = 'DECLARE @az varchar(8000)' +
  @crlf + 'SET @az = ' + QUOTENAME(@chars, '''') +
  @crlf + 'SELECT TOP ' + CAST(@count AS varchar(10)) + ' randstr FROM (' +
  @crlf + 'SELECT DISTINCT' +
  @crlf + '  LEFT('
SET @s2 = ''
WHILE @i > 0
BEGIN
  SET @s2 = @s2 + @crlf + '    SUBSTRING(@az, CAST(FLOOR(RAND(CAST(NEWID()' +
      ' AS binary(4)))*LEN(@az)) AS int)+1, 1) +'
  SET @i = @i - 1
END
SET @s2 = @s2 + ' '''','
SET @s3 = @crlf + '    FLOOR(RAND(CAST(NEWID() AS binary(4)))*' +
    CAST(@long - @short + 1 AS varchar(10))+ ')+' +
    CAST(@short AS varchar(10)) + '+('
SET @i = CAST(1.1 * @count AS int)
SET @j = 0
WHILE @i > 0
BEGIN
  SET @s3 = @s3 + 'B' + CAST(@j AS varchar(10)) + '.n'
  SET @i = @i / 2
  SET @j = @j + 1
  IF @i > 0 SET @s3 = @s3 + '+'
END
SET @s3 = @s3 + ')*0) AS randstr'
SET @s3 = @s3 + @crlf + 'FROM'
SET @i = CAST(1.1 * @count AS int)
SET @j = 0
SET @s4 = ''
WHILE @i > 0
BEGIN
  SET @s4 = @s4 + @crlf + '  (SELECT 0 AS n UNION ALL SELECT ' +
      CAST(POWER(2, @j) AS varchar(20)) + ') AS B' + CAST(@j AS varchar(10))
  SET @i = @i / 2
  SET @j = @j + 1
  IF @i > 0 SET @s4 = @s4 + ','
END
SET @s4 = @s4 + @crlf + ') a ORDER BY NEWID()'
--PRINT @s1 + @s2 + @s3 + @s4
EXEC (@s1 + @s2 + @s3 + @s4)
GO

Edited by - Arnold Fribble on 07/27/2002 09:16:48

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources