Random Unique Number generator RSS

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 09:44 AM|LINK

  ;with cte as
  (
  	select LocalIDNumber,
  	ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS RN,
  	CAST(RAND() * 100000000 as numeric) AS Rn1
  	FROM dbo.Person_Test
  )
  select CASE WHEN ISNULL(LocalIDNumber,'')!='' THEN
  LocalIDNumber ELSE RN+Rn1 END AS RN
  from cte

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  Muhammad Fak...

  Contributor

  2268 Points

  511 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 10:14 AM|LINK

  update dbo.Person_Test
  set LocalIDNumber = CONVERT(int, RAND(CHECKSUM(NEWID())) * 1000000000)
  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.
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 22, 2012 10:50 AM|LINK

  Hi there

  It gives me syntax error here

  ;WITH CTE AS (
  
  it doesnt like the ;
  
  Also change in what we need makes it just a 6 digit number,s hould i just change remove the 7 and 8 from

  SELECT 1, 12345678 UNION

• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 23, 2012 09:36 AM|LINK

  Muhammad Fakhr Elden Sami

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

  Hey Muhammad how do i make it only 6 digits, no more no less.

• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: Random Unique Number generator

  Feb 23, 2012 09:48 AM|LINK

  did u tried my solution i posted yesterday?

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 23, 2012 01:16 PM|LINK

  Yeah, thanks, i posted that i got a bad syntax error with query, what did i do wrong? I am running it on a sql2000 so its an old thing.

• • Reply
  

  Muhammad Fak...

  Contributor

  2268 Points

  511 Posts

  Re: Random Unique Number generator

  Feb 23, 2012 01:27 PM|LINK

  Try this with Left Function 

  
  

  update dbo.Person_Test
  set LocalIDNumber =  Left( Convert(nvarchar, CONVERT(int, RAND(CHECKSUM(NEWID())) * 1000000000)) , 6)
  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 23, 2012 01:28 PM
  • Edited by muhammad fakhr elden sami on Feb 23, 2012 01:28 PM
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 24, 2012 09:35 AM|LINK

  Most excellent Muhammad, now i just need it to be unique as well, so duplicates don't exist. Any good ideas there?

• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: Random Unique Number generator

  Feb 24, 2012 11:21 AM|LINK

  Muhammad Fakhr Elden Sami

  Try this with Left Function 

  
  

  update dbo.Person_Test
  set LocalIDNumber =  Left( Convert(nvarchar, CONVERT(int, RAND(CHECKSUM(NEWID())) * 1000000000)) , 6)
  where LocalIDNumber is null

  Dear Muhammad

  This might give duplicate values as you are truncating the values

  Without truncating

  Convert(nvarchar, CONVERT(int, RAND(CHECKSUM(NEWID())) * 1000000000)) 

  would definitely give you the unique value but it might happen that first 6 characacters are same

  Example

  12345678

  12345689

  Both are diferent value but first six characters are same.

  Dear VxChemical,

  you can use like below to ensure that unique number is generated of fixed 8 digit

  UPDATE dbo.Person_Test
  SET LocalIDNumber =  CAST((99999999-10000000-1 )* RAND(CHECKSUM(NEWID())) + 10000000 AS INT)
  WHERE LocalIDNumber is null

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Feb 24, 2012 11:28 AM
  • Edited by sandeepmittal11 on Feb 24, 2012 11:29 AM
  • Edited by sandeepmittal11 on Feb 24, 2012 11:30 AM
• • Reply
  

  VxChemical

  Member

  28 Points

  133 Posts

  Re: Random Unique Number generator

  Feb 24, 2012 11:48 AM|LINK

  Thanks Sandeepmittal

  Is there no way of using the same code to get 6 unique digits instead of 8, it would work alot better for my barcode system with 6 instead of 8.

  Thank you guys alot for all the help you are providing.