Create a function in SQL Server with a phone number as a parameter and return an arbitrary number
I hope someone can help me here because google is not as ready as I would like. I am relatively new to SQL Server and therefore this is the first function I did myself.
The outline of the function is that it has the phone number varchar (15) as a parameter, it checks that this number is a valid number, that is, it is 8 digits and only contains numbers. The main character I'm trying to avoid is "+". Good number = 12345678 Bad number = +12345678. Once the number has been verified, I would like to generate a random number for each phone number that was sent.
I looked at substrings, a similar operator, Rand (), left (), Right () to do a number search and then produce a random number. I understand that Rand () will produce the same random number if no changes are made, but right now it is actually getting some working code. Any hints of this would be great, or even point to some additional documentation. I have read books on the internet and they have not helped me, maybe I am not looking in the right places.
Here is a piece of code that I was working on 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
a source to share
Just use LIKE and make sure each digit is between 0 and 9.
One way to generate random numbers is CHECKSUM(NEWID())
, or use it as a 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 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)
a source to share
I thought I was updating the post with a solution I came up with for other people who may be looking for something similar. From my research, you cannot use RAND () in UDF. Instead, you need 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)
*/
a source to share