SQL Server Performance Forum – Threads Archive

Random number generation

I have created one asp file for generating random number with the format
XXXXXX-XXXX-XXXXXX-XXXX (where first six character are fixed). Problem : I have generated so many combinations that now i am getting short of combinations,Please help to give me any solution so that my combinations will not get shorten. Any other logic or solution for generating random numbers? Thanks in advance Regards
Rajneesh

---

use newid()
KH

---

why dont you use a GUID ? Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

or else you cna use rand () function SELECT RIGHT(REPLICATE(‘0’,4) + CAST(CAST(RAND() * 10000 AS INT) AS VARCHAR(4)),4) + ‘-‘ + +RIGHT(REPLICATE(‘0’,6) + CAST(CAST(RAND() * 1000000 AS INT) AS VARCHAR(6)),
6) + +’-‘ +RIGHT(REPLICATE(‘0’,4) + CAST(CAST(RAND() * 10000 AS INT) AS VARCHAR(4)),4) —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Hi Guys, Thanks for your update. I cannot use GUID becuase the random numbers are fixed for 20 alpha numberic characters and first 6 characters are fixed. so I need to generate only 14 random alpha numberic characters by considering only few characters that is as below 345679ACDEFGHJKMNPQRSTUVWXY Thanks Regards
Rajneesh

---

select left(replace(newid(), ‘-‘, ”), 14) KH

---

select ‘345679’ +’-‘+ left(newid(),4) + ‘-‘ + left(newid(),6) +’-‘ +left(newid(),4) —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

if i used the to extract the 14 characters from the newid then it will not be unique everytime and another thing is that i need to filter from 345679ACDEFGHJKMNPQRSTUVWXY string. Thanks

---

how about this declare @c varchar (50) ,@i int,@d varchar(15),@j int,@k int
set @c = ‘345679ACDEFGHJKMNPQRSTUVWXY’
set @i = 0
set @k = 0
set @d =”
while @i < 14
begin
while @k = 0
begin
set @j = cast(rand() * 100 as int) if @j < 29
begin
set @k = 1
end
else
begin
Set @k = 0
end
end
set @d [email protected] + substring(@c,@j,1)
set @i = @i +1
set @k = 0
end
select left(@d,4) +’-‘+ substring(@d,5,6) +’-‘+ right(@d,4) —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Let me see if I understand this… The ‘legal’ chars to use is 345679ACDEFGHJKMNPQRSTUVWXY
From this selection, you are to generate a 14-char long unique combination…? So, out of 27 tokens, how many different 14-char combinations would there be?
(I really suck at math, but I do think I got a serious overflow when trying to calculate the number of unique id’s from this) My question is: How is it possible that you’re running out of unique combinations? /Kenneth

---

there will be 174,864,831,8376,960,000 of combiantions ( —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

….and it’s too little…?

---

The first 6 remain static and therefore cannot be counted. The remaining 21 tokens allow for as much as 1.01371E+16 permutations of 14 chars, if I remember correctly. If not, someone please rectify me.
Anyway, 1.01371E+16 is a quite large number that fits in the BIGINT range. What puzzles me is, if this would be "good enough":
SELECT ‘345679’ + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY(21))AS BIGINT)) AS CHAR(21)) —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Care to explain your method of calculating, Dinesh?
Forget it. Must be brain-lifted today. [xx(]
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Bummer, ignore this. Just read the complete thread. [xx(] —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Hi Keneth, Before generating eCoupons i have to check the duplicacy with the existing one that already generated in the system and to get the 14 digit random number from only specified string "345679ACDEFGHJKMNPQRSTUVWXY" Any idea how? Thanks in advance Regards
Rajneesh

---

Hi Franklis,
Before generating eCoupons i have to check the duplicacy with the existing one that already generated in the system and to get the 14 digit random number from only specified string "345679ACDEFGHJKMNPQRSTUVWXY" Any idea how? Thanks in advance Rgards
Rajneesh

---

Any updates

---

If it starts with a fixed string, you can’t really call it random any more. Assuming your MyTable has an identity column (MyIdentityColumn) with a unique constraint: CREATE TRIGGER AddRandomNr_MyTable
FOR INSERT
AS UPDATE MyTable SET MyRandomNrColumn = ‘345679’ + CAST(MyIdentityColumn AS VARCHAR(100))
FROM MyTable INNER JOIN inserted ON MyTable.PK_Column = inserted.PK_Column GO

---

Is this good enough ? declare @i int
[email protected] varchar(100) select @str = ‘345679ACDEFGHJKMNPQRSTUVWXY’ selectsubstring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1) +
substring(@str, abs(convert(bigint, convert(varbinary(10), newid()))) % len(@str) + 1, 1)
KH

---

Any updates

---

What updates are you looking for ?
KH

---

]]>