Random number generator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Random number generator

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 10:36:33

Is there a SQL query that I can that will allow me to populate a field in my database with random numbers?

The field type is varchar(8)

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 10:56:29

Does such a query command exist?

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 10:59:53

In the right upper corner (Google Custom Search) type in "random number" that should bring you a solution or approach.

No, you're never too old to Yak'n'Roll if you're too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 11:03:00

It helped a bit. However I need to generate random numbers to fill into one of my fields? Does this sound possible?

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 11:09:46

Do you want integers or decimals or what? If decimal then to what scale and precision? You want to populate all rows with a single statement?

EDIT:
also if you need the values to be within a certain range then state that as well.

Be One with the Optimizer
TG

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 11:12:19

I'm after just integers that don't appear twice in any other rows. (So everyone has to be unique)

Minimum and maximum is 8 characters. The field I want to update is called ACC_REF

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 11:16:20

since you want integers I'm not sure what you mean by "Minimum and maximum is 8 characters". integers don't have characters

You mean smallest value is: 10000000
and largest value is: 99999999
?

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 11:16:50

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499

select dbo.F_RANDOM_INTEGER(10000000, 99999999, newid())

KH
[spoiler]Time is always against us[/spoiler]

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 11:17:36

Yes thats what i meant...sorry for being a bit unclear

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 11:24:24

select dbo.F_RANDOM_INTEGER(10000000, 99999999, newid())

I dont think thats working. I need to update ACC_REF in my wce_contact table with random numbers between 1000000 and 99999999

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 11:32:01

that was just a tool to help you generate the random number for your specs. You still have to do the update yourself.


update wce_contact set
       acc_ref = dbo.F_RANDOM_INTEGER(10000000, 99999999, newid())

You will also need to deal with your spec of no dupes. Do you need help with that as well?

Be One with the Optimizer
TG

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-11 : 11:54:38

Hmm dupes yes thats a problem...I would appreciate some help on that too

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 12:57:24

add a unique constraint to the column so you can't dupes in there in the first place
OR
try this:


select getdate()
while @@rowcount > 0
begin
       update wc set
              acc_ref = dbo.F_RANDOM_INTEGER(10000000, 99999999, newid())
       from   wce_contact wc
       inner join (
              select acc_ref, min(<primaryKey_of_wce_contact>) as PK
              from   wce_contact 
              group by acc_ref
              having count(*) > 1
              ) d
              on d.acc_ref = wc.acc_ref
              and d.PK <  wc.<primaryKey_of_wce_contact>
end

Then add the constraint to keep it unique

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-12 : 00:10:50

using SQL 2005 / 2008 ?


update  a
set acc_ref = n.num
from
(
    select acc_ref, row_no = row_number() over (order by acc_ref)
    from   wce_contact 
) a
inner join
(
    select num, row_no = row_number() over (order by newid())
    from   f_table_numbers(10000000, 99999999)
) n on a.row_no = n.row_no

reference http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=f_table_numbers

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 02:52:07

or use method 1 and modify with your need
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-28 : 05:41:03

quote:
Originally posted by khtan

using SQL 2005 / 2008 ?

update a
set acc_ref = n.num
from
(
select acc_ref, row_no = row_number() over (order by acc_ref)
from wce_contact
) a
inner join
(
select num, row_no = row_number() over (order by newid())
from f_table_numbers(10000000, 99999999)
) n on a.row_no = n.row_no

reference http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=f_table_numbers

KH
[spoiler]Time is always against us[/spoiler]

When I try running this query, I get the error message

Msg 208, Level 16, State 1, Line 1
Invalid object name 'f_table_numbers'.

Should I be changing anything?

JT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 05:45:49

yes. Get the F_TABLE_NUMBER from the link provided

KH
[spoiler]Time is always against us[/spoiler]

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-28 : 05:53:56

That looks like rather a complex query...to confirm i should be using a query that looks like this? But should I change the value of f_table_number to 99999999

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists
(select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_NUMBER_RANGE]
GO
create function dbo.F_TABLE_NUMBER_RANGE
(
@START_NUMBER int,
@END_NUMBER int
)
/*
This function returns an integer table containing all integers
in the range of@START_NUMBER through @END_NUMBER, inclusive.
The maximum number of rows that this function can return
is 16777216.
*/

returns table
as

return
(
select top 100 percent
NUMBER = (a.NUMBER+b.NUMBER)+
-- Add the starting number for the final result set
-- The case is needed, because the start and end
-- numbers can be passed in any order
case
when @START_NUMBER <= @END_NUMBER
then @START_NUMBER
else @END_NUMBER
end
from
(
Select top 100 percent
NUMBER = convert(int,N01+N02+N03)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 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 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) a
cross join
(
Select top 100 percent
NUMBER =
convert(int,
(N01+N02+N03) *
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 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 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) b
where
a.NUMBER+b.NUMBER <
-- Total number of rows
abs(@START_NUMBER-@END_NUMBER)+1 and
-- Check that the number of rows to be returned
-- is less than or equal to the maximum of 16777216
case
when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
then 1
else 0
end = 1
order by
1
)

GO
GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]
GO

-- Demo using the function to ruturn numbers 1 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)

-- Demo using the function to ruturn numbers -1500 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000)

update a
set acc_ref = n.num
from
(
select acc_ref, row_no = row_number() over (order by acc_ref)
from wce_contact
) a
inner join
(
select num, row_no = row_number() over (order by newid())
from f_table_numbers(10000000, 99999999)
) n on a.row_no = n.row_no

JT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 06:16:48

UPDATE wce_contact
SET acc_ref = ABS(CHECKSUM(NEWID())) % 1000000 + 1000000

N 56°04'39.26"
E 12°55'05.63"

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-28 : 06:46:48

quote:
Originally posted by Peso

UPDATE wce_contact
SET acc_ref = ABS(CHECKSUM(NEWID())) % 1000000 + 1000000

N 56°04'39.26"
E 12°55'05.63"

This query works fine but if i want the numbers to between 10000000 + 99999999 should i change the query to look like this? Also how would I avoid duplicates?

UPDATE wce_contact
SET acc_ref = ABS(CHECKSUM(NEWID())) % 10000000 + 99999999

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2009-09-28 : 09:48:01

Am I right thinking this will work?

UPDATE wce_contact
SET acc_ref = ABS(CHECKSUM(NEWID())) % 10000000 + 99999999

JT

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources