MS SQL Server - Randomizing an existing number

Author

Randomizing an existing number

Dwizz

2006-04-03, 11:23 am

Hey All,

I have a quick question, I have a field called telephone number, which
contains yes you got it customer telephone numbers, I was wondering if
it is possible to randomize the telephone number that already exists?
so that for example a customers telephone number is: 0800 909 9793
reads 0208 457 4259. If so, how would one go about coding it?

TIA

Dhwiren

Jack Vamvas

2006-04-03, 11:23 am

I'm not quite sure what data type you are using for the existing column ,
but you could convert the telephone number into a INT.
set that as a seed for the RAND function (see BOL) and then reconvert back
to prefered data type.

Is this for a SELECT or an UPDATE function?

--
Jack Vamvas
____________________
_______________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"Dwizz" <Dwizz20@yahoo.co.uk> wrote in message
news:1144078599.588361.40890@i39g2000cwa.googlegroups.com...
> Hey All,
>
> I have a quick question, I have a field called telephone number, which
> contains yes you got it customer telephone numbers, I was wondering if
> it is possible to randomize the telephone number that already exists?
> so that for example a customers telephone number is: 0800 909 9793
> reads 0208 457 4259. If so, how would one go about coding it?
>
> TIA
>
> Dhwiren
>

Dwizz

2006-04-03, 1:23 pm

Hey Jack,

Thanks for the quick reply, its for a SELECT function, the telephone
number is already set to INT, I wasn't sure if I could use the RAND
function, as most examples I've seen suggests its used to generate
Random Numbers from scratch, where as in my case I already have numbers
which I want randomized

Jack Vamvas wrote:[color=darkred
]
> I'm not quite sure what data type you are using for the existing column ,
> but you could convert the telephone number into a INT.
> set that as a seed for the RAND function (see BOL) and then reconvert back
> to prefered data type.
>
> Is this for a SELECT or an UPDATE function?
>
> --
> Jack Vamvas
> ____________________
_______________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
>
> "Dwizz" <Dwizz20@yahoo.co.uk> wrote in message
> news:1144078599.588361.40890@i39g2000cwa.googlegroups.com...

amish

2006-04-03, 1:23 pm

Try this

declare @id bigint
select @id = rand() * 9999999999
select case when @id < 1000000000 then @id + 1000000000
when @id > 9999999999 then @id - 1000000000
else @id end as val

Regards
Amish Shah

Dwizz

2006-04-04, 7:25 am

Hey Amish,

I gave your code a try, I believe thats what I'm after, but
unfortunately I couldn't get it to work - I'm still learning SQL, I've
attached my code below, could someone please point out where I've gone
wrong.

TIA
Dhwiren

SET NOCOUNT ON

DECLARE @counter smallint
DECLARE @counterLimit smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
DECLARE @ValidCharactersLeng
th int
DECLARE @DEBUG int

-- P fields
DECLARE @Phone bigint

-- @Phone
SET @counter=0
SET @counterLimit = len(@Phone)
IF @counterLimit>0
BEGIN
SET @Phone=''
WHILE @counter < @counterLimit
BEGIN
-- create a random sting
SELECT @Phone = RAND()*9999999999
SELECT CASE WHEN @Phone <1000000000 THEN @Phone + 1000000000
WHEN @Phone >9999999999 THEN @Phone - 1000000000
ELSE @Phone END AS Val

IF @DEBUG=1 PRINT @Phone
END

amish

2006-04-04, 9:23 am

You can not need to assign '' to @phone since its int.
You also dont need to pass any phone number since it returns random
number.

ALTER PROCEDURE RANDOMNUM (@DEBUG INT)
as
BEGIN

SET NOCOUNT ON
DECLARE @counter smallint
DECLARE @counterLimit smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
DECLARE @ValidCharactersLeng
th int

DECLARE @Phone bigint
-- P fields

-- @Phone

SET @Phone=0
-- create a random sting
SELECT @Phone = RAND()*9999999999
SELECT CASE WHEN @Phone <1000000000 THEN @Phone + 1000000000
WHEN @Phone >9999999999 THEN @Phone - 1000000000
ELSE @Phone END AS Val

IF @DEBUG=1 PRINT @Phone
END

exec Randomnum @debug = 1

Regards
Amish Shah

Dwizz

2006-04-04, 9:23 am

Thanks Amish & Jack for all your help, I've got it to work now.

Thank you guys very much

Dhwiren

Doug

2006-04-06, 8:23 pm

homework.l

Sponsored Links