🚀 go-pugleaf

Q�������SQL Server 2005��2008��ʹ��������ʽ��

A��
��SQL Server 2005��2008�����������ʽ�������Զ��幦�ܷdz����㣬��Ϊ��֧��CL
R�������ζ����ֻ��Ҫ����һ���Զ���ij��򼯲�����

�����ѡ����Visual Studio 2005��2008��ʹ��VB.NET��C#��ѡ��SQL Server��Ŀ����
��Ŀ����֮������Ҫ�һ���Ŀ->���->�û��Զ��庯�������𣬳��򼯾ͻᱻ��
����ݿ��У������û��Զ��庯�������ó��򼯡����������������Զ��庯��һ��
��������

����ִ�����µ�SQL���������CLRִ�У�

sp_configure 'clr enabled',1
reconfigure

��Ҫע����ǣ�����Ҫ������[SqlFunction]��ӵ����з�����ǰ�档����ͷ���ֵ����
ΪSQL���ͣ����磺SqlChars, SqlString, SqlInt32�ȵȡ�

����Ϊ�����Ҫ������ʽ������ʵ�ַ���

1. RegexMatch - ��������������ҵ�ģʽ��Ϊ1����ȻΪ0
2. RegexReplace - �����������з�ϵ��������ض��ַ����
3. RegexSelectOne - ���ص�1��2��3��������ƥ�������
4. RegexSelectAll - ��������ƥ���ֵ���÷ָ�����зָ�

ʹ�÷������£�
select dbo.RegexMatch( N'123-45-6749', N'^\d{3}-\d{2}-\d{4}
����ϵ绰�����ģʽ�򷵻�1

select dbo.RegExReplace('Remove1All3Letters7','[a-zA-Z]','')
����137

select dbo.RegexSelectOne('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', 
0)
����123-45-6789

select dbo.RegexSelectAll('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', 
'|')
����123-45-6749|222-33-4444

����������£�
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text; 


public partial class UserDefinedFunctions
{ 


    public static readonly RegexOptions Options = 
RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline; 


    [SqlFunction]
    public static SqlBoolean RegexMatch(SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex(pattern.Value, Options);
        return regex.IsMatch(new string(input.Value));
    } 


    [SqlFunction]
    public static SqlString RegexReplace(SqlString expression, SqlString 
pattern, SqlString replace)
    {
        if (expression.IsNull || pattern.IsNull || replace.IsNull)
            return SqlString.Null; 


        Regex r = new Regex(pattern.ToString());
        return new SqlString(r.Replace(expression.ToString(), 
replace.ToString()));
    } 


    // returns the matching string. Results are separated by 3rd parameter
    [SqlFunction]
    public static SqlString RegexSelectAll(SqlChars input, SqlString 
pattern, SqlString matchDelimiter)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value)); 


        StringBuilder sb = new StringBuilder();
        while (results.Success)
        {
            sb.Append(results.Value); 


            results = results.NextMatch(); 


            // separate the results with newline|newline
            if (results.Success)
            {
                sb.Append(matchDelimiter.Value);
            }
        } 


        return new SqlString(sb.ToString()); 


    } 


    // returns the matching string
    // matchIndex is the zero-based index of the results. 0 for the 1st 
match, 1, for 2nd match, etc
    [SqlFunction]
    public static SqlString RegexSelectOne(SqlChars input, SqlString 
pattern, SqlInt32 matchIndex)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value)); 


        string resultStr = "";
        int index = 0; 


        while (results.Success)
        {
            if (index == matchIndex)
            {
                resultStr = results.Value.ToString();
            } 


            results = results.NextMatch();
            index++; 


        } 


        return new SqlString(resultStr); 


    } 


}; 


�����زο���
http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expressi
on-Replace.aspx

�Ż�������ʽ����:
http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

http://www.codeproject.com/KB/string/SqlRegEx.aspx 
--
Welcome to Database Board
��ӭ������ݿ��
http://bbs.sjtu.edu.cn/bbsdoc?board=database

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.31]