build random phone number.

I'm building a test database and I need to randomly create phone numbers in the format xxxxxxxxxx

I have 5000 contacts and need to generate fake phone numbers for them. anyone know how I can write an update query to do this?

ScAndalThe way I can think to do this is:

1. create a View that will return a random number (this is needed because the RAND() function cannot be called from another function (the one we are creating in step 2 below))

CREATE VIEW dbo.vRandNumber
AS
SELECT RAND() AS RandomNumber

2. write a GenerateRandomNumber UDF:

CREATE FUNCTION GenerateRandomNumber(@.Min int, @.Max int)
RETURNS float
AS
BEGIN
 RETURN @.Min + (select RandomNumber from vRandNumber) * (@.Max-@.Min)
END

3. write a GenerateRandomPhoneNumber UDF:

CREATE FUNCTION dbo.GenerateRandomPhoneNumber ()
RETURNS varchar(10) AS
BEGIN
 DECLARE @.myNumber int, @.myPhoneNumber varchar(9), @.X smallint
 SELECT @.X = 0, @.myPhoneNumber =''
 WHILE @.X < 10
BEGIN
SELECT @.myNumber=dbo.generaterandomnumber(0,9)
SELECT @.myPhoneNumber = @.myPhoneNumber + CAST(@.myNumber AS CHAR(1))
SELECT @.X = @.X + 1
END
 RETURN @.myPhoneNumber
END

4. populate the fake phone numbers like this:

UPDATE contacts SET PhoneNumber = GenerateRandomPhoneNumber()

This is just my first thought. Someone else might come along and do it in 2 steps ;-)
Terri|||I can do it in 1 step! Assuming that table has a identity column you can use it as a seed for the rand() function:

update Contact
 set PhoneNumber = cast(cast(rand(ContactID * 12345)*90000 as int) + 10000 as varchar)
 + cast(cast(rand(ContactID * 54321)*90000 as int) + 10000 as varchar)

I had to do 5 digits at a time to avoid the int size problem, and I had to multiply the identity column with a largeish number to make the phone numbers look random.

For some reason
rand(X) - rand(X + 1) = -1.8633e-005
for all values of X

Just noticed it. Seems wrong to me. Ah well. Just don't use this to randomize lotto numbers and you'll be fine.|||Here is a variation of Terri's:

--create a view to expose NEWID() for randomness
create view dbo.vwRandomNumGenerator
as
select top 10 n
from
(
select 0 n 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 9 union all
select 9
 ) d
order by newid()
go
--create udf
create function dbo.udfPhoneNumberGenerator ()
returns char(10)
AS
begin
declare @.phoneNumber char(10)
set @.phoneNumber = ''
Select @.phoneNumber = n + @.phoneNumber
from
(
select convert(char(1),n) n
from dbo.vwRandomNumGenerator
) d
return @.phoneNumber
end
go
--call our rnd phone generator inline
select dbo.udfPhoneNumberGenerator() RandomPhoneNumber
--drop view vwRandomNumGenerator
--drop function udfPhoneNumberGenerator

|||Thanks guys! That worked perfectly.

Great advice!

ScAndal