Creating a Function in SQL Server with a Phone Number as a parameter and returns a Random Number

Question

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

Answer 1

Simply use LIKE and ensure each digit is between 0 and 9.

One way to generate random numbers is CHECKSUM(NEWID()), or use this as the seed for RAND

IF @phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    SELECT @NewNumber = LEFT(
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)) +
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)) +
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)), 15)

Or the double negative LIKE with length check

IF @phone NOT LIKE '%[^0-9]%' AND LEN(@phone) = 8
    SELECT @NewNumber = LEFT(
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)) +
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)) +
             CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)), 15)

Answer 2

I thought I would update my post with the solution I have come up with for other people who may be searching for something similar. From my research you are unable to use RAND() within a UDF. Instead you have to create a view and call it from that view.

Create Function [dbo].[AlterPhone](@Phone Varchar(15))
Returns varchar (15)
AS 
BEGIN
declare @Counter int
declare @NewNumber varchar(15)
set @NewNumber = 0

select @NewNumber = case when len(@Phone)=8 and isnumeric(@Phone)    = 1 
then (select RandValue from dbo.vw_RandomVarchar) else 'Bad Number' end
return @NewNumber
END

/*
CREATE VIEW [dbo].[vw_RandomVarchar]
AS
  SELECT cast(cast(rand()*100000000 as int)as varchar) AS RandValue

END

SELECT dbo.AlterPhone(12345678)
*/