编写SQL函数以使用正则表达式验证电子邮件，移动号码，PAN号码，GSTIN号码，HSN_CODE号码。 [英] Write SQL function to validate email, mobile no, PAN no, GSTIN no, HSN_CODE using regex.
问题描述
我想编写SQL函数来验证电子邮件，MobileNO和PAN卡...在SQL中使用RegEX，然后更新表中的数据，
我试过下面的代码，但它在SQL Server 2008 R2中不起作用
我尝试过：
创建功能[BILLING]。[RegExMatch]
（
@Value varchar（50），
@ValidationType varchar（50）
）
返回VARCHAR（50）
AS
BEGIN
声明@Result位= 0，@ Regx varchar（200）
IF（@ ValidationType ='EMAIL'）
BEGIN
SET @Regx ='[a-zA-Z0-9_\ - ] + @（[a-zA-Z0-9_\ - ] + \。）+（com | org | edu | | nz | au）'
IF（dbo.RegExMatch（@ Value，@ Regx）= 1）
BEGIN
SET @ Result = 1
END
ELSE
BEGIN
SET @ Result = 0
END
结束
返回@Result;
END
I want to write SQL function to validate Email, MobileNO, And PAN card... using RegEX in SQL, before update Data in Table,
I have tried below code but it's not working in SQL Server 2008 R2
What I have tried:
CREATE FUNCTION [BILLING].[RegExMatch]
(
@Value varchar(50),
@ValidationType varchar(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
Declare @Result bit=0,@Regx varchar(200)
IF(@ValidationType='EMAIL')
BEGIN
SET @Regx='[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz|au)'
IF (dbo.RegExMatch(@Value,@Regx)=1)
BEGIN
SET @Result=1
END
ELSE
BEGIN
SET @Result=0
END
END
return @Result;
END
推荐答案
SQL Server 2008 R2不直接公开正则表达式功能。如果 PATINDEX [< a href =https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017target =_ blanktitle =新窗口> ^ ]不能胜任您可以使用CLR功能的任务 - 请参阅对这篇文章 [ ^ ]。
如果您认为自己正在做的事情，请检查您是否正确配置了它 - 例如你是否开启了Ole Automation功能 - 更多细节在该链接上
SQL Server 2008 R2 does not expose regex functionality directly. If PATINDEX[^] is not up to your task you could use a CLR function - see the responses to this post[^].
If that is what you think you are doing then check you have configured it properly - e.g. do you have Ole Automation features switched on - more detail is on that link
为什么你想在游戏中这么晚进行验证？
在我看来，数据库可以做的最好的事情是存储数据，验证它的最佳位置是在应用程序中。
在我看来，启动验证过程的最佳位置是在最终用户第一次投入时的前端。
如果您使用的是网络页面这可能是javascript（jquery）验证，它会让最终用户知道在提交之前有错误。
一旦提交，在应用程序级别重新验证。如果出现错误，您只需返回包含用户错误列表的网页。
关于我在数据库级别进行的唯一验证是确保条目不为NULL。
所有这一切的另一个好处是将错误信息发送给最终用户的步骤更少。在报名表级别，它是立即的。在应用程序级别，它至少是一个操作。在数据库级别，它至少还有2个级别。
另一个好处是有API和&用于验证/验证这些项目的服务。
使用system.net.mail可以在NET应用程序中轻松验证电子邮件。
有人建议在数据库中添加CLR功能。这将是一个.NET项目本身，并且会像在应用程序级别那样做。虽然这是可行的，但这需要您有权将CLR添加到您的服务器。
Why do you want to do the validation so late in the game?
In my opinion, the best thing the database can do is to store the data, and the best place to validate it is in the application.
In my opinion, the best place to start the validation process is on the front end, when the end user is first putting it in.
If you are using a web page this could be javascript(jquery) validation which would let the end user know there was an error before they hit submit.
Once they do submit, re-validate at the application level. If there is an error you can just return the web page with a list of errors for the user.
About the only validation I would do at the DB level is to make sure the entries are not NULL.
Another benefit to all of this is sending the error info back to the end user has less steps. At the "entry form" level it is immediate. At the application level it is at least one operation. At the DB level it is at least 2 more levels.
Yet another benefit is that there are APIs & Services out there to validate/verify these items.
Emails can easily be validated within a NET application by using system.net.mail.
Someone has suggested to add a CLR function into your DB. This would be a NET project all by itself and would do much the same as if you did it at the application level. While it is feasible, this would require you to have permissions to add CLRs to your server.
这篇关于编写SQL函数以使用正则表达式验证电子邮件，移动号码，PAN号码，GSTIN号码，HSN_CODE号码。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
