Post reply

Random numbers generation SP

  • January 25, 2007 at 5:52 am

    #153401

    Hi,

    I would like to highlight some back ground before telling you the problem.

    Background - I have to generate the 20 digit random number which should be unique, and the first 6 characters are fixed for example C7116A(fixed)-XXXX-XXXXXX-XXXX. I have developed the ASP page which is using randomize function and (Int(27 * Rnd + 1)) in loop. We have a fixed group of characters for generating the 14 digit random numbers that are "345679ACDEFGHJKMNPQRSTUVWXY". One more thing to highlight that while inserting the random number in the table i am checking whether the new random number already exists or not. If exists i am looking for the another random number else insert into database

    Problem : My table which is storing the random numbers are now very high in volume i am getting problems like

    1) short of combinations

    2) getting time out error

    3) taking too much time

    Could you please let me know the right way to resolve this type of problem. I will be highly grateful to you.

    Waiting for your ealiest reply.

    Thanks

    Regards

    Rajneesh

  • January 25, 2007 at 6:08 am

    #685494

    You might have a look at GUIDs...

    SELECT

    NEWID()

    --Ramesh

  • January 25, 2007 at 6:37 am

    #685503

    it sounds like you need just a random number of a fixed length, which you are appending to the first 6 fixed characters.

    you already identified that a random number does not guarantee uniqueness. NEWID()  that Romesh suggested does. you should consider getting rid of your 20 digit string and simply use the newID. it resolves the issue of checking for duplicates, and again guarantees uniqueness. you can simply keep track of every number you generate, instead of forcing the number to start with a specific sequence.

    otherwise, consider something like this: this is all numeric, but there are other ways to get a random letter

    --C7116A(fixed)-XXXX-XXXXXX-XXXX

    select ' C7116A-'

    + right('00000000' + convert(varchar,convert(int,27 * Rand() + 1 )),4) + '-'

    + right('00000000' + convert(varchar,convert(int,27 * Rand() + 1 )),6)+ '-'

    + right('00000000' + convert(varchar,convert(int,27 * Rand() + 1 )),4)

    results:

     C7116A-0016-000011-0017

     C7116A-0006-000006-0014

     C7116A-0017-000017-0008

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 25, 2007 at 6:52 am

    #685507

    here's an example of random letters:

    select ' C7116A-'

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + '-'

    --6 chars

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + '-'

    --4 chars

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    + char(65 + convert(int, 25 * Rand() + 1 ))

    results:

     C7116A-HDXF-LDRUUI-SBLR

     C7116A-VNIR-HHEVCY-EXRM

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 25, 2007 at 7:45 am

    #685532

    This will return a random value that is not in your table.

    Your scheme can have 10 to the 20th power combinations, so as long as the value is randomly generated there is little chance of duplicates, even with billions of rows in the table.  This generatates 1000 random values, and returns the first one that is not in your table.

    select top 1
RAND_STRING
from
(
select
 RAND_STRING = 
  'C7116A-'+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+'-'+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+'-'+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+
  substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)
from
  ( select top 1000 x = '345679ACDEFGHJKMNPQRSTUVWXY' from syscolumns ) aa ) a
where
 a.RAND_STRING not in ( select b.RAND_STRING from MyTableWithRandString b )
     
     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply