Random Unique Number generator RSS

• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Random Unique Number generator

  Feb 22, 2012 07:19 AM|LINK

  I have a table with a long list of people, and one of the columns is a LocalIDNumber, some of the rows have a 8 digit number it, but some has NULL.
  
  What i need to do is, fill out the ones that has NULL and give them a unique 8 digit number.
  
  Is there a great and easy way to generate the numbers with an Sql Query?

• • Reply
  

  Muhammad Fak...

  Contributor

  2268 Points

  511 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 07:30 AM|LINK

  I think that will help 

  ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

  I use the upper and the lower as 8 digits (1's, 9's)

  Update tbl

  set field = ROUND(((9999999 -11111111 -1) * RAND() + 11111111), 0)

  where field is null

  MCTS
  If you feel it helps, Mark as answered so that it can help others to find solution.
  For Any further questions, please contact me.
  • Edited by muhammad fakhr elden sami on Feb 22, 2012 07:32 AM
  • Edited by muhammad fakhr elden sami on Feb 22, 2012 07:33 AM
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 07:35 AM|LINK

  Sorry i am new at this, how do i specify which table and column i want it to populate.

  It would be my dbo.Person_Test table and my LocalIDNumber column

• • Reply
  

  Muhammad Fak...

  Contributor

  2268 Points

  511 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 07:50 AM|LINK

  Try this 

  update dbo.Person_Test
  set LocalIDNumber = CONVERT(int, RAND(CHECKSUM(NEWID())) * 100000000)
  where LocalIDNumber is null

  MCTS
  If you feel it helps, Mark as answered so that it can help others to find solution.
  For Any further questions, please contact me.
  • Edited by muhammad fakhr elden sami on Feb 22, 2012 07:50 AM
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 08:09 AM|LINK

  Thank you Muhmammad

  It did it somewhat. But maybe its because i changed what i wanted.

  We changed it so that we would like 6 Digits instead of 8.

  I just tried changing the code to this

  update dbo.Person_Test
  set LocalIDNumber = CONVERT(int, RAND(CHECKSUM(NEWID())) * 100000)

  now it comes out with either 4 or 5 digits.

  And are these Unique? no duplicates?

  Thanks again for all your help

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 08:12 AM|LINK

  --1st query query to generate random number
  ;with n as
  (
  	select 1 as num union all select 2 union all select 3 union all select 4 union all select 5 union all
  	select 6 union all select 7 union all select 8 union all select 9
  ),
  numbers as
  (
  	select ROW_NUMBER() over ( order by n1.num) as num from N N1,N N2,N N3,N N4,N N5
  )
  select distinct top 1 LEFT( CONVERT(varchar(max),ABS(BINARY_CHECKSUM(NEWID()))) +  CONVERT(varchar(max),ABS(BINARY_CHECKSUM(NEWID()))) +  CONVERT(varchar(max),ABS(BINARY_CHECKSUM(NEWID()))),8)
  from  numbers
  
  
  
  --2nd query to generate random number
  SELECT CAST(RAND() * 100000000 as numeric) AS [RandomNumber]

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 09:05 AM|LINK

  Hey thanks Yogi.

  How would i match your code with my database and table

  table name is dbo.Person_Test and the Column name is LocalIDNumber.

  Thanks alot, also if i wanted to change this to be six digits instead would i use a different number than 100000000

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 09:12 AM|LINK

  VxChemical

  How would i match your code with my database and table

  table name is dbo.Person_Test and the Column name is LocalIDNumber

  use below code for your table..

  select CASE WHEN LocalIDNumber is null then
  CAST(RAND() * 100000000 as numeric)
  else LocalIDNumber END AS LocalIDNumber
  FROM dbo.Person_Test

  VxChemical

  Thanks alot, also if i wanted to change this to be six digits instead would i use a different number than 100000000

  you can change the number.

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 09:25 AM|LINK

  I think i made a mistake, using the code there set them all to the same number.

• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 09:36 AM|LINK

  Try this below script. This would ensure

  1. LocalIDNumber would be generated of unique 8 digit number

  2. Would not update from any of the existing values

  /*
  CREATE table Person(
  	Id INT, LocalIDNumber INT
  )
  
  INSERT INTO Person
  SELECT 1, 12345678 UNION ALL SELECT 2, NULL UNION ALL SELECT 3, NULL
  */
  
  WHILE ((SELECT COUNT(1) FROM Person WHERE LocalIDNumber IS NULL) > 0)
  BEGIN
  	;WITH CTE AS (
  		SELECT Id, CAST((99999999-10000000-1 )* RAND(CHECKSUM(NEWID())) + 10000000 AS INT) as rand_value
  		FROM Person WHERE LocalIDNumber IS NULL
  	)
  
  	UPDATE Person SET LocalIDNumber = rand_value
  	FROM (
  		SELECT Id, rand_value FROM CTE
  		EXCEPT
  		SELECT Id, LocalIDNumber FROM Person
  	) TAB WHERE Person.Id = TAB.Id
  end
  
  SELECT * FROM Person

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Feb 22, 2012 09:37 AM