Translate this in a stored procedure
-
I have the code shown below in a VB code which I need to run in a Stored Procedure.
This code needs to create a 6 digits code. I am looping to build the code either with a letter or number.
Is it possible to "translate" it and use this in a SP ?
Dim s(25) As String
s(1) = "A"
s(2) = "B"
s(3) = "C"
s(4) = "D"
s(5) = "E"
s(6) = "F"
s(7) = "G"
s(8) = "H"
s(9) = "J"
s(10) = "K"
s(11) = "L"
s(12) = "M"
s(13) = "N"
s(14) = "P"
s(15) = "Q"
s(16) = "R"
s(17) = "S"
s(18) = "T"
s(19) = "U"
s(20) = "V"
s(21) = "W"
s(22) = "X"
s(23) = "Y"
s(24) = "Z"
For j = 1 To 6 ' Build a 6 digit code
Randomize()
MyValue = Int((2 * Rnd()) + 1) ' Letter or number ?
If MyValue = 1 Then ' Number
Randomize()
MyValue = Int((8 * Rnd()) + 1)
sNumero = sNumero & MyValue
Else ' Letter
Randomize()
MyValue = Int((24 * Rnd()) + 1)
sNumero = sNumero & s(MyValue)
End If
Next
Jean-Luc
www.corobori.com -
If you goal is to generate a random six character string, then this seems to work. The main problem in generating random numbers in SQL Server is providing an appropriate seed value for the RAND() function.
DECLARE @MyCode varchar(6) DECLARE @Rnd int
SET @MyCode = '' SET @Seed = DATEPART(ms, GETDATE()) * DATEPART(ms, GETDATE())
WHILE LEN(@MyCode) < 6 BEGIN
SET @Seed = @Seed + (@Seed * RAND(@Seed)) SET @MyCode = @MyCode + CHAR((RAND(@Seed) * 26.0) + 65)
END
PRINT @MyCode
--------------------
Colt 45 - the original point and click interface
-
Thanks, you just missed the Declare @Seed int but otherwise it's fine
Jean-Luc
www.corobori.com -
Ahhh ... renamed all but the DECLARE ... DOH!!!
Also, dredged this up which works as well.
DECLARE @MyCode varchar(6) DECLARE @Min int DECLARE @Max int;
SET @MyCode = '' SET @Min = 65 SET @Max = 90
WHILE LEN(@MyCode) < 6 BEGIN SET @MyCode = @MyCode + CHAR(Round(((@Max - @Min -1) * Rand() + @Min), 0)) END
PRINT @MyCode
And here is a variation that uses SUBSTRING to select from a range of characters you specify,
DECLARE @MyCode varchar(6) DECLARE @Valid varchar(100) DECLARE @Max int
SET @MyCode = '' SET @Valid = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz SET @Valid = @Valid + '0123456789-=[];,./~!@#$%^&*()_+|{}:<>?' SET @Max = LEN(@Valid)WHILE LEN(@MyCode) < 6 BEGIN SET @MyCode = @MyCode + SUBSTRING(@Valid, CAST((((@Max * Rand()) + 1)) as int), 1) END
PRINT @MyCode
Of course with these samples you could remove the WHILE loop and copy the CHAR/SUBSTRING for as many characters as you need.
--------------------
Colt 45 - the original point and click interface -
Here's a set-based method...
SELECT CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65) AS MyCode
Also, I may be incorrect, but I don't believe any of these methods posted, so far, guarantee uniqueness although approximately 6^36th is a heck of a lot of combinations.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff, thanks for illustrating what I said
"Of course with these samples you could remove the WHILE loop and copy the CHAR/SUBSTRING for as many characters as you need."
--------------------
Colt 45 - the original point and click interface -
You say 'em, I'll make 'em. What a team!
The neat thing here is that using NEWID as the seed for RAND allows true set based ops... using RAND with any other seed might allow for adjacent duplication... try the following...SELECT TOP 100
CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)
+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65) AS MyCode
FROM dbo.SYSCOMMENTS
'course... using a Tally table as a row control would be even better
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Why not just use the newid() function to generate the random string.
declare @MyCode varchar(6)
while @MyCode is null or @MyCode like '%[I,O]%'
set @MyCode = left(convert(varchar(40), newid()), 6)
select @MyCode
-
Sorry oversight in previous post that newid() will generate a hex value (i.e. only A-F).
You can use a view to generate the random number and a generic function to construct a random string of alphanumeric characters or any length and excluding any set of character (in this case I & O).
create view v_RandomNumber
as
select rand() as Value
go
create function dbo.funRandChar(@Length int, @Exclude varchar(36))
returns varchar(1000)
as
begin
declare @result varchar(1000),
@charval int
set @result = ''
while len(@result) != @length
begin
while @charval is null or char(@charval) like '[' + @Exclude + ']'
begin
set @charval = (select convert(int, Value * 36) from v_RandomNumber)
if @charval < 10
set @charval = @charval + 48
else
set @charval = @charval + 55
end
set @result = @result + char(@charval)
set @charval = null
end
return(@result)
end
go
-- return a 6 character random string excluding I & O
select dbo.funRandChar(6, 'IO')
-
A possible alternative
DECLARE @chars varchar(255), @code varchar(6)
SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
SET @code = ''
SELECT @code = @code + [char]
FROM
(SELECT TOP 6 SUBSTRING(@chars,number,1) AS [char]
FROM master.dbo.spt_values WHERE type = 'P' AND number between 1 AND LEN(@chars)
ORDER BY NEWID()) x
SELECT @code
(adjust @chars to limited chars used)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply