字符串实用工具函数示例

项目
05/08/2013

字符串实用工具示例包含用 Visual C# 和 Visual Basic 编写的流式表值函数，该函数可以将逗号分隔的字符串拆分为具有一列的表。它也包含一个将字符串列转换为逗号分隔的字符串的聚合函数。同时实现的还有一个标量函数和一个表值函数，这些函数提供正则表达式替换和正则表达式搜索功能。

若要实现流式表值函数，请创建返回实现 IEnumerable 接口的对象的方法。此 IEnumerable 方法必须通过某个属性链接至其他填充表值函数的行的方法。

先决条件

若要创建和运行此项目，必须安装下列软件：

SQL Server 或 SQL Server Express。您可以从 SQL Server Express Documentation and Samples （Express 文档和示例）网站免费获取 SQL Server Express。
SQL Server 开发人员网站提供的 AdventureWorks 数据库
.NET Framework SDK 2.0 或更高版本，或 Microsoft Visual Studio 2005 或更高版本。您可以免费获取 .NET Framework SDK。
此外，还必须满足以下条件：
您使用的 SQL Server 实例必须已启用 CLR 集成。
若要启用 CLR 集成，请执行以下步骤：

启用 CLR 集成
- 执行以下 Transact-SQL 命令：
sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

注意

若要启用 CLR 集成，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。
必须在您使用的 SQL Server 实例上安装 AdventureWorks 数据库。
如果您不是要使用的 SQL Server 实例的管理员，则必须让管理员授予您 CreateAssembly 权限，才能完成安装。

注意
若要启用 CLR 集成，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。

生成示例

按照以下说明创建和运行该示例：

打开 Visual Studio 或 .NET Framework 命令提示符。
如有必要，为您的示例创建目录。对于此示例，我们将使用 C:\MySample。
在 c:\MySample 中，创建 StringUtils.vb（用于 Visual Basic 示例）或 StringUtils.cs（用于 C# 示例），并将相应的 Visual Basic 或 C# 示例代码（如下所示）复制到该文件中。
从命令行提示符执行以下代码之一（具体取决于所选的语言)，对示例代码进行编译。
- Vbc /target:library /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll",C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug- /target:library StringUtils.vb
- Csc/reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library StringUtils.cs
将 Transact-SQL 安装代码复制到一个文件中，并在示例目录中将其另存为 Install.sql。
如果该示例安装在 C:\MySample\ 之外的目录中，请按说明编辑文件 Install.sql 以指向该位置。
通过执行以下命令部署程序集和存储过程：
- sqlcmd -E -I -i install.sql
- 将 Transact-SQL 测试命令脚本复制到一个文件中，并在示例目录中将其另存为 test.sql。
使用以下命令执行测试脚本：
- sqlcmd -E -I -i test.sql
将 Transact-SQL 清除脚本复制到一个文件中，并在示例目录中将其另存为 cleanup.sql。
使用以下命令执行该脚本：
- sqlcmd -E -I -i cleanup.sql

示例代码

下面是此示例的代码列表。

using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using System.Data.Sql;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

    /// <summary>
    /// This class is provides regular expression operations for Transact-SQL callers
    /// </summary>
    public sealed class RegularExpression
    {
        private RegularExpression()
        {

        }

        /// <summary>
        /// This method returns a table of matches, groups, and captures based on the input
        /// string and pattern string provided.
        /// </summary>
        /// <param name="sqlInput">What to match against</param>
        /// <param name="sqlPattern">What to look for</param>
        /// <returns>An object which appears to be reading from SQL Server but which in fact is reading
        ///          from a memory based representation of the data.</returns>
        [SqlFunction(FillRowMethodName = "FillRow")]
        public static IEnumerable Matches(SqlString sqlInput, SqlString sqlPattern)
        {
            string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value;
            string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value;

            return GetMatches(input, pattern);
        }

        public static void FillRow(object obj, out int matchId, out int matchIndex, out string matchValue,
            out int groupId, out int groupIndex, out string groupValue, out int captureIndex,
            out string captureValue)
        {
            MatchResult result = (MatchResult)obj;
            matchId = result.MatchID;
            matchIndex = result.MatchIndex;
            matchValue = result.MatchValue;
            groupId = result.GroupID;
            groupIndex = result.GroupIndex;
            groupValue = result.GroupValue;
            captureIndex = result.CaptureIndex;
            captureValue = result.CaptureValue;
        }

        /// <summary>
        ///     Generates a list of Match/Group/Capture tuples represented using the
        ///     MatchResult struct based on the regular expression match of the input
        ///     string and pattern string provided.
        /// </summary>
        /// <param name="input">What to match</param>
        /// <param name="pattern">What to look for</param>
        /// <returns>A list of Match/Group/Capture tuples</returns>
        private static List<MatchResult> GetMatches(string input, string pattern)
        {
            List<MatchResult> result = new List<MatchResult>();
            int matchId = 0;
            int groupId = 0;
            foreach (Match m in Regex.Matches(input, pattern))
            {
                if (m.Groups.Count < 1)
                    result.Add(new MatchResult(matchId, m.Index, m.Value, -1, -1, string.Empty, -1, string.Empty));
                else
                {
                    groupId = 0;
                    foreach (Group g in m.Groups)
                    {
                        if (g.Captures.Count < 1)
                            result.Add(new MatchResult(matchId, m.Index, m.Value,
                                groupId, g.Index, g.Value, -1, string.Empty));
                        else
                        {
                            foreach (Capture c in m.Groups)
                            {
                                result.Add(new MatchResult(matchId, m.Index, m.Value,
                                    groupId, g.Index, g.Value, c.Index, c.Value));
                            }
                        }

                        groupId += 1;
                    }
                }

                matchId += 1;
            }

            return result;
        }

        /// <summary>
        ///     This method performs a pattern based substitution based on the provided input string, pattern
        ///     string, and replacement string.
        /// </summary>
        /// <param name="sqlInput">The source material</param>
        /// <param name="sqlPattern">How to parse the source material</param>
        /// <param name="sqlReplacement">What the output should look like</param>
        /// <returns></returns>
        public static string Replace(SqlString sqlInput, SqlString sqlPattern, SqlString sqlReplacement)
        {
            string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value;
            string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value;
            string replacement = (sqlReplacement.IsNull) ? string.Empty : sqlReplacement.Value;
            return Regex.Replace(input, pattern, replacement);
        }
    }

    /// <summary>
    /// This struct is used trepresents a Match/Group/Capture tuple.  Instances of this struct are
    /// created by the GetMatches method.
    /// </summary>
    internal struct MatchResult
    {
        /// <summary>
        /// Which match this is
        /// </summary>
        private int _matchID;
        public int MatchID
        {
            get
            {
                return this._matchID;
            }
        }

        /// <summary>
        /// Where the match starts in the input string
        /// </summary>
        private int _matchIndex;
        public int MatchIndex
        {
            get
            {
                return this._matchIndex;
            }
        }

        /// <summary>
        /// What string matched the pattern
        /// </summary>
        private string _matchValue;
        public string MatchValue
        {
            get
            {
                return this._matchValue;
            }
        }

        /// <summary>
        /// Which matching group this is
        /// </summary>
        private int _groupID;
        public int GroupID
        {
            get
            {
                return this._groupID;
            }
        }

        /// <summary>
        /// Where this group starts in the input string
        /// </summary>
        private int _groupIndex;
        public int GroupIndex
        {
            get
            {
                return this._groupIndex;
            }
        }

        /// <summary>
        /// What the group matched in the input string
        /// </summary>
        private string _groupValue;
        public string GroupValue
        {
            get
            {
                return this._groupValue;
            }
        }

        /// <summary>
        /// Where this capture starts in the input string
        /// </summary>
        private int _captureIndex;
        public int CaptureIndex
        {
            get
            {
                return this._captureIndex;
            }
        }

        /// <summary>
        /// What the capture matched in the input string
        /// </summary>
        private string _captureValue;
        public string CaptureValue
        {
            get
            {
                return this._captureValue;
            }
        }

        /// <summary>
        ///     A convenient constructor which fills in all the fields contained in this struct.
        /// </summary>
        /// <param name="matchID">Which match this is</param>
        /// <param name="matchIndex">Where the match starts in the input string</param>
        /// <param name="matchValue">What string matched the pattern</param>
        /// <param name="groupID">Which matching group this is</param>
        /// <param name="groupIndex">Where this group starts in the input string</param>
        /// <param name="groupValue">What the group matched in the input string</param>
        /// <param name="captureIndex">Where this capture starts in the input string</param>
        /// <param name="captureValue">What the capture matched in the input string</param>
        public MatchResult(int matchId, int matchIndex, string matchValue,
            int groupId, int groupIndex, string groupValue,
            int captureIndex, string captureValue)
        {
            this._matchID = matchId;
            this._matchIndex = matchIndex;
            this._matchValue = matchValue;
            this._groupID = groupId;
            this._groupIndex = groupIndex;
            this._groupValue = groupValue;
            this._captureIndex = captureIndex;
            this._captureValue = captureValue;
        }
    }


    public sealed class StringSplitter
    {

        /// <summary>
        /// The streaming table-valued function used to split the string into a relation
        /// </summary>
        /// <param name="argument"></param>
        /// <returns></returns>
        [SqlFunction(FillRowMethodName = "FillRow")]
        public static IEnumerable Split(SqlString argument)
        {
            string value;
            if (argument.IsNull)
                value = "";
            else
                value = argument.Value;
            return value.Split(',');
        }

        public static void FillRow(Object obj, out string stringElement)
        {
            stringElement = (string)obj;
        }

        /// <summary>
        /// Don't allow callers to create instances of this class
        /// </summary>
        private StringSplitter() { }
    }
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
        Microsoft.SqlServer.Server.Format.UserDefined, //use clr serialization to serialize the intermediate result
        IsInvariantToNulls = true,//optimizer property
        IsInvariantToDuplicates = false,//optimizer property
        IsInvariantToOrder = false,//optimizer property
        MaxByteSize = 8000)//maximum size in bytes of persisted value
        ]
    public class Concatenate : Microsoft.SqlServer.Server.IBinarySerialize
    {
        /// <summary>
        /// The variable that holds the intermediate result of the concatenation
        /// </summary>
        private StringBuilder intermediateResult;

        /// <summary>
        /// Initialize the internal data structures
        /// </summary>
        public void Init()
        {
            intermediateResult = new StringBuilder();
        }

        /// <summary>
        /// Accumulate the next value, nop if the value is null
        /// </summary>
        /// <param name="value"></param>
        public void Accumulate(SqlString value)
        {

            if (value.IsNull)
            {
                return;
            }
            intermediateResult.Append(value.Value).Append(',');

        }

        /// <summary>
        /// Merge the partially computed aggregate with this aggregate.
        /// </summary>
        /// <param name="other"></param>
        public void Merge(Concatenate other)
        {
            intermediateResult.Append(other.intermediateResult);
        }

        /// <summary>
        /// Called at the end of aggregation, to return the results of the aggregation
        /// </summary>
        /// <returns></returns>
        public SqlString Terminate()
        {
            string output = string.Empty;
            //delete the trailing comma, if any
            if (intermediateResult != null && intermediateResult.Length > 0)
                output = intermediateResult.ToString(0, intermediateResult.Length - 1);
            return new SqlString(output);
        }

        public void Read(BinaryReader r)
        {
            if (r == null) throw new ArgumentNullException("r");
            intermediateResult = new StringBuilder(r.ReadString());
        }

        public void Write(BinaryWriter w)
        {
            if (w == null) throw new ArgumentNullException("w");
            w.Write(intermediateResult.ToString());
        }
    }

VB.NET

Imports Microsoft.VisualBasic
Imports Microsoft.SqlServer.Server
Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.IO
Imports System.Linq
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Text.RegularExpressions


<Serializable(), Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Microsoft.SqlServer.Server.Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
Public Class Concatenate
    'use clr serialization to serialize the intermediate result
    'optimizer property
    'optimizer property
    'optimizer property
    'maximum size in bytes of persisted value
    Implements Microsoft.SqlServer.Server.IBinarySerialize

    ''' <summary>
    ''' The variable that holds the intermediate result of the concatenation
    ''' </summary>
    Private intermediateResult As StringBuilder

    ''' <summary>
    ''' Initialize the internal data structures
    ''' </summary>
    Public Sub Init()
        intermediateResult = New StringBuilder()
    End Sub

    ''' <summary>
    ''' Accumulate the next value, nop if the value is null
    ''' </summary>
    ''' <param name="value"></param>
    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then
            Return
        End If

        intermediateResult.Append(value.Value).Append(","c)
    End Sub

    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="other"></param>
    Public Sub Merge(ByVal other As Concatenate)
        intermediateResult.Append(other.intermediateResult)
    End Sub

    ''' <summary>
    ''' Called at the end of aggregation, to return the results of the aggregation
    ''' </summary>
    ''' <returns></returns>
    Public Function Terminate() As SqlString
        Dim output As String = String.Empty

        'delete the trailing comma, if any
        If Not (intermediateResult Is Nothing) AndAlso intermediateResult.Length > 0 Then
            output = intermediateResult.ToString(0, intermediateResult.Length - 1)
        End If

        Return New SqlString(output)
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
        If r Is Nothing Then
            Throw New ArgumentNullException("r")
        End If
        intermediateResult = New StringBuilder(r.ReadString())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
        If w Is Nothing Then
            Throw New ArgumentNullException("w")
        End If
        w.Write(intermediateResult.ToString())
    End Sub
End Class

Public NotInheritable Class RegularExpression

    Private Sub New()
    End Sub

    ''' <summary>
    ''' This method returns a table of matches, groups, and captures based on the input
    ''' string and pattern string provided.
    ''' </summary>
    ''' <param name="sqlInput">What to match against</param>
    ''' <param name="sqlPattern">What to look for</param>
    ''' <returns>An object which appears to be reading from SQL Server but which in fact is reading
    '''          from a memory based representation of the data.</returns>
    <SqlFunction(Name:="RegexMatches", FillRowMethodName:="FillMatchRow", _
        TableDefinition:="MatchID int, MatchIndex int, MatchValue nvarchar(4000), GroupID int, GroupIndex int, GroupValue nvarchar(4000), CaptureIndex int, CaptureValue nvarchar(4000)")> _
    Public Shared Function Matches(ByVal sqlInput As SqlString, ByVal sqlPattern As SqlString) As IEnumerable
        Dim input As String = String.Empty
        If Not sqlInput.IsNull Then
            input = sqlInput.Value
        End If

        Dim pattern As String = String.Empty
        If Not sqlPattern.IsNull Then
            pattern = sqlPattern.Value
        End If

        Return GetMatches(input, pattern)
    End Function

    ''' <summary>
    ''' Invoked by SQL Server when returning a row of the TVF.  Splits the MatchResult object into
    ''' the separate pieces of data which will form the columns of the row.
    ''' </summary>
    ''' <param name="row"></param>
    ''' <param name="matchID"></param>
    ''' <param name="matchIndex"></param>
    ''' <param name="matchValue"></param>
    ''' <param name="groupID"></param>
    ''' <param name="groupIndex"></param>
    ''' <param name="groupValue"></param>
    ''' <param name="captureIndex"></param>
    ''' <param name="captureValue"></param>

    Private Shared Sub FillMatchRow(ByVal row As Object, <Out()> ByRef matchId As Integer, _
        <Out()> ByRef matchIndex As Integer, <Out()> ByRef matchValue As String, <Out()> ByRef groupId As Integer, _
        <Out()> ByRef groupIndex As Integer, <Out()> ByRef groupValue As String, <Out()> ByRef captureIndex As Integer, _
        <Out()> ByRef captureValue As String)

        Dim result As MatchResult

        result = CType(row, MatchResult)

        matchId = result.MatchID
        matchIndex = result.MatchIndex
        matchValue = result.MatchValue
        groupId = result.GroupID
        groupIndex = result.GroupIndex
        groupValue = result.GroupValue
        captureIndex = result.CaptureIndex
        captureValue = result.CaptureValue
    End Sub

    ''' <summary>
    '''     Generates a list of Match/Group/Capture tuples represented using the
    '''     MatchResult struct based on the regular expression match of the input
    '''     string and pattern string provided.
    ''' </summary>
    ''' <param name="input">What to match</param>
    ''' <param name="pattern">What to look for</param>
    ''' <returns>A list of Match/Group/Capture tuples</returns>
    Private Shared Function GetMatches(ByVal input As String, ByVal pattern As String) As List(Of MatchResult)
        Dim result As List(Of MatchResult) = New List(Of MatchResult)()
        Dim matchID As Integer = 0
        Dim groupID As Integer = 0

        For Each m As Match In Regex.Matches(input, pattern)
            If m.Groups.Count < 1 Then
                result.Add(New MatchResult(matchID, m.Index, m.Value, -1, -1, _
                    String.Empty, -1, String.Empty))
            Else
                groupID = 0
                For Each g As Group In m.Groups
                    If g.Captures.Count < 1 Then
                        result.Add(New MatchResult(matchID, m.Index, m.Value, _
                            groupID, g.Index, g.Value, -1, String.Empty))
                    Else
                        For Each c As Capture In m.Groups
                            result.Add(New MatchResult(matchID, m.Index, _
                                m.Value, groupID, g.Index, g.Value, c.Index, _
                                c.Value))
                        Next
                    End If

                    groupID += 1
                Next
            End If

            matchID += 1
        Next

        Return result
    End Function

    ''' <summary>
    '''     This method performs a pattern based substitution based on the provided input string, pattern
    '''     string, and replacement string.
    ''' </summary>
    ''' <param name="sqlInput">The source material</param>
    ''' <param name="sqlPattern">How to parse the source material</param>
    ''' <param name="sqlReplacement">What the output should look like</param>
    ''' <returns></returns>
    <SqlFunction(Name:="RegexReplace", DataAccess:=DataAccessKind.None)> _
    Public Shared Function Replace(ByVal sqlInput As SqlString, ByVal sqlPattern As SqlString, ByVal sqlReplacement As SqlString) As String
        Dim input As String = String.Empty

        If Not sqlInput.IsNull Then
            input = sqlInput.Value
        End If

        Dim pattern As String = String.Empty

        If Not sqlPattern.IsNull Then
            pattern = sqlPattern.Value.ToString()
        End If

        Dim replacement As String = String.Empty

        If Not sqlReplacement.IsNull Then
            replacement = sqlReplacement.Value.ToString()
        End If

        Return Regex.Replace(input, pattern, replacement)
    End Function
End Class


''' <summary>
''' This struct is used to represent a Match/Group/Capture tuple.  Instances of 
''' this struct are created by the GetMatches method.
''' </summary>
Friend Structure MatchResult
    ''' <summary>
    ''' Which match this is
    ''' </summary>
    Private _matchID As Integer


    Friend ReadOnly Property MatchID() As Integer
        Get
            Return Me._matchID
        End Get
    End Property

    ''' <summary>
    ''' Where the match starts in the input string
    ''' </summary>
    Private _matchIndex As Integer


    Friend ReadOnly Property MatchIndex() As Integer
        Get
            Return Me._matchIndex
        End Get
    End Property

    ''' <summary>
    ''' What string matched the pattern
    ''' </summary>
    Private _matchValue As String

    Friend ReadOnly Property MatchValue() As String
        Get
            Return Me._matchValue
        End Get
    End Property

    ''' <summary>
    ''' Which matching group this is
    ''' </summary>
    Private _groupID As Integer


    Friend ReadOnly Property GroupID() As Integer
        Get
            Return Me._groupID
        End Get
    End Property

    ''' <summary>
    ''' Where this group starts in the input string
    ''' </summary>
    Private _groupIndex As Integer


    Friend ReadOnly Property GroupIndex() As Integer
        Get
            Return Me._groupIndex
        End Get
    End Property

    ''' <summary>
    ''' What the group matched in the input string
    ''' </summary>
    Private _groupValue As String

    Friend ReadOnly Property GroupValue() As String
        Get
            Return Me._groupValue
        End Get
    End Property

    ''' <summary>
    ''' Where this capture starts in the input string
    ''' </summary>
    Private _captureIndex As Integer


    Friend ReadOnly Property CaptureIndex() As Integer
        Get
            Return Me._captureIndex
        End Get
    End Property

    ''' <summary>
    ''' What the capture matched in the input string
    ''' </summary>
    Private _captureValue As String


    Friend ReadOnly Property CaptureValue() As String
        Get
            Return Me._captureValue
        End Get
    End Property

    ''' <summary>
    '''     A convenient constructor which fills in all the fields contained in this struct.
    ''' </summary>
    ''' <param name="matchID">Which match this is</param>
    ''' <param name="matchIndex">Where the match starts in the input string</param>
    ''' <param name="matchValue">What string matched the pattern</param>
    ''' <param name="groupID">Which matching group this is</param>
    ''' <param name="groupIndex">Where this group starts in the input string</param>
    ''' <param name="groupValue">What the group matched in the input string</param>
    ''' <param name="captureIndex">Where this capture starts in the input string</param>
    ''' <param name="captureValue">What the capture matched in the input string</param>
    Friend Sub New(ByVal matchID As Integer, ByVal matchIndex As Integer, ByVal matchValue As String, ByVal groupID As Integer, ByVal groupIndex As Integer, ByVal groupValue As String, ByVal captureIndex As Integer, ByVal captureValue As String)
        Me._matchID = matchID
        Me._matchIndex = matchIndex
        Me._matchValue = matchValue
        Me._groupID = groupID
        Me._groupIndex = groupIndex
        Me._groupValue = groupValue
        Me._captureIndex = captureIndex
        Me._captureValue = captureValue
    End Sub
End Structure
Public NotInheritable Class StringSplitter

    ''' <summary>
    ''' The streaming table-valued function used to split the string into a relation
    ''' </summary>
    ''' <param name="argument"></param>
    ''' <returns></returns>
    <SqlFunction(Name:="Split", DataAccess:=DataAccessKind.None, FillRowMethodName:="FillSplitRow", _
        TableDefinition:="StringElement nvarchar(128) COLLATE Latin1_General_CI_AS")> _
    Public Shared Function Split(ByVal argument As SqlString) As IEnumerable
        Dim value As String

        If argument.IsNull Then
            value = String.Empty
        Else
            value = argument.Value
        End If

        Return value.Split(","c)
    End Function


    Private Shared Sub FillSplitRow(ByVal row As Object, ByRef stringElement As String)
        stringElement = CType(row, String)
    End Sub

    ''' <summary>
    ''' Don't allow callers to create instances of this class
    ''' </summary>
    Private Sub New()
    End Sub
End Class

这是 Transact-SQL 安装脚本 (Install.sql)，该脚本在数据库中部署程序集并创建存储过程。

USE AdventureWorks
GO

IF OBJECT_ID(N'RegexMatches', N'FT') is not null
DROP Function RegexMatches;
GO

IF OBJECT_ID(N'Split', N'FT') is not null
DROP Function Split;
GO

IF OBJECT_ID(N'RegexReplace', N'FS') is not null
DROP Function RegexReplace;
GO

IF OBJECT_ID(N'Concatenate', N'AF') is not null
DROP Aggregate Concatenate;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'StringUtils')
DROP ASSEMBLY StringUtils;
GO

DECLARE @SamplePath nvarchar(1024)
-- You will need to modify the value of the this variable if you have installed the sample someplace other than the default location.
Set @SamplePath = 'C:\MySample\'
CREATE ASSEMBLY [StringUtils] 
FROM @SamplePath + 'StringUtils.dll'
WITH permission_set = Safe;
GO

CREATE AGGREGATE [dbo].[Concatenate](@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME [StringUtils].[Concatenate];
GO

CREATE FUNCTION [dbo].[Split](@input nvarchar(4000)) 
RETURNS  TABLE(StringElement nvarchar(128) COLLATE Latin1_General_CI_AS)
AS EXTERNAL NAME [StringUtils].[StringSplitter].[Split];
GO

CREATE FUNCTION [RegexMatches] (@input nvarchar(max), @pattern nvarchar(max))
RETURNS TABLE(
MatchID int,
    MatchIndex int,
    MatchValue nvarchar(4000),
GroupID int,
GroupIndex int,
GroupValue nvarchar(4000),
CaptureIndex int,
CaptureValue nvarchar(4000))
AS EXTERNAL NAME [StringUtils].[RegularExpression].[Matches];
GO

CREATE FUNCTION [RegexReplace] (@input nvarchar(max), @pattern nvarchar(max), @replacement nvarchar(max))
RETURNS nvarchar(max)
AS EXTERNAL NAME [StringUtils].[RegularExpression].[Replace]
GO

这是 test.sql，该脚本通过执行函数测试该示例。

USE AdventureWorks
GO

-- Invoke the tvf
SELECT * FROM dbo.Split('will,this,work');
GO

-- Invoke the aggregate over the results of the tvf
SELECT dbo.Concatenate(StringElement) FROM dbo.Split('will,this,also,work');
GO

-- Find two word pairs where the first word contains an 'r'
SELECT MatchID, MatchIndex, MatchValue, 
  GroupID, GroupIndex, GroupValue, 
  CaptureIndex, CaptureValue
FROM dbo.RegexMatches('The quick red fox jumped over the lazy brown dog', '(\w*r\w*)\s(\w+)');
GO

-- A variant of the above with no backtracking
SELECT MatchID, MatchIndex, MatchValue, 
  GroupID, GroupIndex, GroupValue, 
  CaptureIndex, CaptureValue
FROM dbo.RegexMatches('The quick red fox jumped over the lazy brown dog', '(?>\w*r\w*)\s(?>\w+)');
GO

-- Swap the subject of the sentence with the object of the sentence.
SELECT dbo.RegexReplace('The quick red fox jumped over the lazy brown dog', 
'^The (?<fox>(?:[\w]+\s){3})jumped over the (?<dog>(?:[\w]+\s){2}(?:[\w]+))$',

'The ${dog} jumped over the ${fox}');

下面的 Transact-SQL 从数据库中删除程序集和函数。

USE AdventureWorks
GO

IF OBJECT_ID(N'RegexMatches', N'FT') is not null
DROP Function RegexMatches;
GO

IF OBJECT_ID(N'Split', N'FT') is not null
DROP Function Split;
GO

IF OBJECT_ID(N'RegexReplace', N'FS') is not null
DROP Function RegexReplace;
GO

IF OBJECT_ID(N'Concatenate', N'AF') is not null
DROP Aggregate Concatenate;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'StringUtils')
DROP ASSEMBLY StringUtils;

GO

请参阅

概念

公共语言运行时 (CLR) 集成的使用方案和示例

通过

字符串实用工具函数示例

先决条件

启用 CLR 集成

生成示例

按照以下说明创建和运行该示例：

示例代码

请参阅

概念

其他资源