SQL Server Performance

Random number generation

Discussion in 'SQL Server 2005 General Developer Questions' started by rajneesh, Feb 6, 2007.

  1. rajneesh New Member

    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
  2. khtan New Member

    use newid()


    KH
  3. Roji. P. Thomas New Member

  4. dineshasanka Moderator

    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/
  5. rajneesh New Member

    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
  6. khtan New Member


    select left(replace(newid(), '-', ''), 14)



    KH
  7. dineshasanka Moderator

    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/
  8. rajneesh New Member

    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
  9. dineshasanka Moderator

    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 =@d + 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/
  10. Kewin New Member

    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
  11. dineshasanka Moderator

    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/
  12. Kewin New Member

    ....and it's too little...?
  13. FrankKalis Moderator

    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
  14. FrankKalis Moderator

    quote:Originally posted by dineshasanka

    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/

    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
  15. FrankKalis Moderator

    quote:Originally posted by FrankKalis
    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))

    Bummer, ignore this. Just read the complete thread. [xx(]

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  16. rajneesh New Member

    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
  17. rajneesh New Member

    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
  18. rajneesh New Member

    Any updates
  19. Adriaan New Member

    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
  20. khtan New Member

    Is this good enough ?



    declare @i int
    declare@str 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
  21. rajneesh New Member

    Any updates
  22. khtan New Member

    quote:Originally posted by rajneesh

    Any updates

    What updates are you looking for ?


    KH

Share This Page