用电话号码作为参数在SQL Server中创建函数并返回一个随机数 [英] Creating a Function in SQL Server with a Phone Number as a parameter and returns a Random Number

问题描述

我希望有人能够帮助我，因为谷歌并不像我希望的那样即将到来。我对SQL Server相对来说比较陌生，所以这是我自己设定的第一个功能。

函数的大纲是它有一个电话号码varchar（ 15）作为参数，它检查这个数字是一个合适的数字，即它是8位数字，只包含数字。我试图避免的主角是'+'。好号码= 12345678错误号码= [phone]。一旦数字被检查，我想为每个传入的电话号码产生一个随机数。

我查看了子字符串，类似运算符Rand（） ，left（），Right（）以搜索数字，然后生成一个随机数。我知道Rand（）会产生相同的随机数，除非对它做了修改，但现在它实际上是获得一些工作代码。对此的任何提示都会很棒，甚至可以指向更多的文档。我已经在线阅读书籍，他们没有帮助我，也许我没有找到正确的地方。

这是我在Rand工作的一段代码

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ （15）
set @Phone ='12345678'
set @Counter = len（@Phone）

@Counter> 0
begin
在@Phone中选择'％[0-9]％'然后将其转换为int（rand（）* 100000000）else'Bad Number'end
set @counter = @counter - 1
end
return

感谢您提前帮忙

Emer

解决方案

只需使用LIKE并确保每个数字位于0和$ 9.

生成随机数的一种方法是使用 CHECKSUM（NEWID（））种子为RAND

 如果@phone LIKE'[0-9] [0-9] [0-9] [0- '
 SELECT @NewNumber = LEFT（
 CAST（ABS（CHECKSUM（NEWID（）））AS varchar（0）[0-9] [0-9] [0-9] （10））+ 
 CAST（ABS（CHECKSUM（NEWID（）））AS varchar（10））+ 
 CAST（ABS（CHECKSUM（NEWID（）））varchar（10）），15 ）
  

或者双重否定LIKE与长度检查

 如果@phone NOT LIKE'％[^ 0-9]％'AND LEN（@phone）= 8 
 SELECT @ NewNumber = LEFT（
 CAST（ABS（CHECKSUM（NEWID（）））varchar（10））+ 
 CAST（ABS（CHECKSUM（NEWID（）））varchar（10））+ 
 CAST（ABS（CHECKSUM（NEWID（）））AS varchar（10）），15）
  

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 = [phone]. 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

解决方案

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)

这篇关于用电话号码作为参数在SQL Server中创建函数并返回一个随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！