Hi Guys,

I am hoping someone can help me here as google is not being as forthcoming as I would have liked. I am relatively new to SQL Server and so this is the first function I have set myself to do.

The outline of the function is that it has a Phone number varchar(15) as a parameter, it checks that this number is a proper number, i.e. it is 8 digits long and contains only numbers. The main character I am trying to avoid is '+'. Good Number = 12345678 Bad Number = +12345678. Once the number is checked I would like to produce a random number for each phone number that is passed in.

I have looked at substrings, the like operator, Rand(), left(), Right() in order to search through the number and then produce a random number. I understand that Rand() will produce the same random number unless alterations are done to it but right now it is about actually getting some working code. Any hints on this would be great or even point me towards some more documentation. I have read books online and they haven't helped me, maybe I am not looking in the right places.

Here is a snippet of code I was working on the Rand

declare @Phone Varchar (15)
declare @Counter Varchar (1)
declare @NewNumber Varchar(15)
set @Phone = '12345678'
set @Counter = len(@Phone)

while @Counter > 0
begin
   select case when @Phone like '%[0-9]%' then  cast(rand()*100000000 as int) else 'Bad Number' end
   set @counter = @counter - 1
end
return 

Thanks for the help in advance

Emer