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
BEGINDECLARE @.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
ENDRETURN @.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|||Thanks guys! That worked perfectly.
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
Great advice!
ScAndal
