Randomizing an existing number

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

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
>

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... 

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

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

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

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

Thank you guys very much

Dhwiren

homework.l