Mikel
[转载]如何在SQL Server中使用正则表达式 – 陈希章 – 博客园.
最近处理客户一个需求，是要在SQL Server中对某个列进行校验，使用正则表达式保证该列必须是一个邮件地址。
我们都知道，SQL Server的T-SQL中默认是没有这样的语法的。找了一些资料，下面这个例子还不错，分享给大家参考
http://www.codeproject.com/KB/database/xp_pcre.aspx
大致步骤是
1.下载他提供的那个压缩包，里面有源代码和安装脚本
2.将DLL复制到SQL Server规定的目录
3.运行INSTALL.sql这个脚本
大致使用的效果如下
SELECT master.dbo.fn_pcre_match('billg@microsoft.com','^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$')
这句脚本的意思是，根据后面的正则表达式（一个email的规则）匹配前面的字符串.如果返回1的话，表示匹配到了，否则返回0.很显然，
很显然，我们可以根据这个做法设置某个字段的约束。例如下面这样
上述做法是通过扩展存储过程来实现的功能，需要通过C++来编写。
当然，如果SQL Server 是2005或者以后的版本，也可以通过托管代码来实现。关于这个话题的基本概念，你可以参考 http://msdn.microsoft.com/en-us/library/ms254498(v=vs.80).aspx
我写好了一个例子，给大家参考：
<span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">.</span><span class="typ">SqlClient</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">.</span><span class="typ">SqlTypes</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">Microsoft</span><span class="pun">.</span><span class="typ">SqlServer</span><span class="pun">.</span><span class="typ">Server</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Collections</span><span class="pun">;</span><span class="pln"></span><span class="kwd">using</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Text</span><span class="pun">.</span><span class="typ">RegularExpressions</span><span class="pun">;</span><span class="pln"></span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">partial</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> </span><span class="typ">UserDefinedFunctions</span><span class="pln"></span><span class="pun">{</span><span class="pln"> </span><span class="com">/// <summary></span><span class="pln"> </span><span class="com">/// 这是一个进行正则表达式验证的函数.作者：陈希章</span><span class="pln"> </span><span class="com">/// </summary></span><span class="pln"> </span><span class="com">/// <param name="ValidOption">选项，0为用户自定义，1为网址，2为邮件地址，3为邮政编码</param></span><span class="pln"> </span><span class="com">/// <param name="ValidString">要验证的字符串</param></span><span class="pln"> </span><span class="com">/// <param name="ValidPatten">用户自定义的正则表达式规则</param></span><span class="pln"> </span><span class="com">/// <returns></returns></span><span class="pln"> </span><span class="pun">[</span><span class="typ">Microsoft</span><span class="pun">.</span><span class="typ">SqlServer</span><span class="pun">.</span><span class="typ">Server</span><span class="pun">.</span><span class="typ">SqlFunction</span><span class="pun">]</span><span class="pln"> </span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">bool</span><span class="pln"> </span><span class="typ">RegExValidate</span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> </span><span class="typ">ValidOption</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> </span><span class="typ">ValidString</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> </span><span class="typ">ValidPatten</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> strRegExPatten </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">switch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ValidOption</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="lit">0</span><span class="pun">:</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> strRegExPatten </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ValidPatten</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="lit">1</span><span class="pun">:</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> strRegExPatten </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="str">"^[a-zA-Z0-9\-\.]+\.(com|org|net|mil|edu|COM|ORG|NET|MIL|EDU)$"</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="lit">2</span><span class="pun">:</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> strRegExPatten </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="str">"^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$"</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="lit">3</span><span class="pun">:</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> strRegExPatten </span><span class="pun">=</span><span class="pln"> </span><span class="pun">@</span><span class="str">"^[0-9]{4}\s{0,1}[a-zA-Z]{2}$"</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">break</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Regex</span><span class="pun">.</span><span class="typ">IsMatch</span><span class="pun">(</span><span class="typ">ValidString</span><span class="pun">,</span><span class="pln">strRegExPatten</span><span class="pun">))</span><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">;</span><span class="pln"> </span><span class="kwd">else</span><span class="pln"> </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">false</span><span class="pun">;</span><span class="pln"> </span><span class="pun">}</span><span class="pln"></span><span class="pun">};</span> |
