--- title: "String Utility Functions Sample | Microsoft Docs" ms.custom: "" ms.date: "06/13/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: "database-engine" ms.topic: "reference" ms.assetid: 9623013f-15f1-4614-8dac-1155e57c880c author: mashamsft ms.author: mathoma manager: craigg --- # String Utility Functions Sample The String Utilities sample contains a streaming table-valued function written in Visual C# and Visual Basic, which splits a comma-separated string into a table with one column. It also contains an aggregate function that converts a string column to a comma-separated string. Also implemented are a scalar function and a table-valued function that provide regular expression replacement and regular expression searching functionality. To implement a streaming table-valued function, create a method that returns an object that implements the `IEnumerable` interface. This `IEnumerable` method must be linked by an attribute to another method which fills in rows of the table-valued function. ## Prerequisites To create and run this project the following the following software must be installed: - [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express. You can obtain [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express free of charge from the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express Documentation and Samples [Web site](https://www.microsoft.com/download/details.aspx?id=42299) - The AdventureWorks database that is available at the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Developer [Web site](https://archive.codeplex.com/?p=SqlServerSamples) - .NET Framework SDK 2.0 or later or Microsoft Visual Studio 2005 or later. You can obtain .NET Framework SDK free of charge. - In addition, the following conditions must be met: - The [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using must have CLR integration enabled. - In order to enable CLR integration, perform the following steps: #### Enabling CLR Integration - Execute the following [!INCLUDE[tsql](../../includes/tsql-md.md)] commands: `sp_configure 'clr enabled', 1` `GO` `RECONFIGURE` `GO` > [!NOTE] > To enable CLR integration, you must have `ALTER SETTINGS` server level permission, which is implicitly held by members of the `sysadmin` and `serveradmin` fixed server roles. - The AdventureWorks database must be installed on the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using. - If you are not an administrator for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using, you must have an administrator grant you **CreateAssembly** permission to complete the installation. ## Building the Sample #### Create and run the sample by using the following instructions: 1. Open a Visual Studio or .NET Framework command prompt. 2. If necessary, create a directory for your sample. For this example, we will use C:\MySample. 3. In c:\MySample, create `StringUtils.vb` (for the Visual Basic sample) or `StringUtils.cs` (for the C# sample) and copy the appropriate Visual Basic or C# sample code (below) into the file. 4. Compile the sample code from the command line prompt by executing one of the following, depending on your choice of language. - `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` 5. Copy the [!INCLUDE[tsql](../../includes/tsql-md.md)] installation code into a file and save it as `Install.sql` in the sample directory. 6. If the sample is installed in a directory other then `C:\MySample\`, edit the file `Install.sql` as indicated to point to that location. 7. Deploy the assembly and stored procedure by executing - `sqlcmd -E -I -i install.sql` - Copy [!INCLUDE[tsql](../../includes/tsql-md.md)] test command script into a file and save it as `test.sql` in the sample directory. 8. Execute the test script with the following command - `sqlcmd -E -I -i test.sql` 9. Copy the [!INCLUDE[tsql](../../includes/tsql-md.md)] cleanup script into a file and save it as `cleanup.sql` in the sample directory. 10. Execute the script with the following command - `sqlcmd -E -I -i cleanup.sql` ## Sample Code The following are the code listings for this sample. C# ``` 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; /// /// This class is provides regular expression operations for Transact-SQL callers /// public sealed class RegularExpression { private RegularExpression() { } /// /// This method returns a table of matches, groups, and captures based on the input /// string and pattern string provided. /// /// What to match against /// What to look for /// An object which appears to be reading from SQL Server but which in fact is reading /// from a memory based representation of the data. [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; } /// /// 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. /// /// What to match /// What to look for /// A list of Match/Group/Capture tuples private static List GetMatches(string input, string pattern) { List result = new List(); 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; } /// /// This method performs a pattern based substitution based on the provided input string, pattern /// string, and replacement string. /// /// The source material /// How to parse the source material /// What the output should look like /// 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); } } /// /// This struct is used trepresents a Match/Group/Capture tuple. Instances of this struct are /// created by the GetMatches method. /// internal struct MatchResult { /// /// Which match this is /// private int _matchID; public int MatchID { get { return this._matchID; } } /// /// Where the match starts in the input string /// private int _matchIndex; public int MatchIndex { get { return this._matchIndex; } } /// /// What string matched the pattern /// private string _matchValue; public string MatchValue { get { return this._matchValue; } } /// /// Which matching group this is /// private int _groupID; public int GroupID { get { return this._groupID; } } /// /// Where this group starts in the input string /// private int _groupIndex; public int GroupIndex { get { return this._groupIndex; } } /// /// What the group matched in the input string /// private string _groupValue; public string GroupValue { get { return this._groupValue; } } /// /// Where this capture starts in the input string /// private int _captureIndex; public int CaptureIndex { get { return this._captureIndex; } } /// /// What the capture matched in the input string /// private string _captureValue; public string CaptureValue { get { return this._captureValue; } } /// /// A convenient constructor which fills in all the fields contained in this struct. /// /// Which match this is /// Where the match starts in the input string /// What string matched the pattern /// Which matching group this is /// Where this group starts in the input string /// What the group matched in the input string /// Where this capture starts in the input string /// What the capture matched in the input string 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 { /// /// The streaming table-valued function used to split the string into a relation /// /// /// [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; } /// /// Don't allow callers to create instances of this class /// 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 { /// /// The variable that holds the intermediate result of the concatenation /// private StringBuilder intermediateResult; /// /// Initialize the internal data structures /// public void Init() { intermediateResult = new StringBuilder(); } /// /// Accumulate the next value, nop if the value is null /// /// public void Accumulate(SqlString value) { if (value.IsNull) { return; } intermediateResult.Append(value.Value).Append(','); } /// /// Merge the partially computed aggregate with this aggregate. /// /// public void Merge(Concatenate other) { intermediateResult.Append(other.intermediateResult); } /// /// Called at the end of aggregation, to return the results of the aggregation /// /// 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 _ 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 ''' ''' The variable that holds the intermediate result of the concatenation ''' Private intermediateResult As StringBuilder ''' ''' Initialize the internal data structures ''' Public Sub Init() intermediateResult = New StringBuilder() End Sub ''' ''' Accumulate the next value, nop if the value is null ''' ''' Public Sub Accumulate(ByVal value As SqlString) If value.IsNull Then Return End If intermediateResult.Append(value.Value).Append(","c) End Sub ''' ''' Merge the partially computed aggregate with this aggregate. ''' ''' Public Sub Merge(ByVal other As Concatenate) intermediateResult.Append(other.intermediateResult) End Sub ''' ''' Called at the end of aggregation, to return the results of the aggregation ''' ''' 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 ''' ''' This method returns a table of matches, groups, and captures based on the input ''' string and pattern string provided. ''' ''' What to match against ''' What to look for ''' An object which appears to be reading from SQL Server but which in fact is reading ''' from a memory based representation of the data. _ 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 ''' ''' 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. ''' ''' ''' ''' ''' ''' ''' ''' ''' ''' Private Shared Sub FillMatchRow(ByVal row As Object, ByRef matchId As Integer, _ ByRef matchIndex As Integer, ByRef matchValue As String, ByRef groupId As Integer, _ ByRef groupIndex As Integer, ByRef groupValue As String, ByRef captureIndex As Integer, _ 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 ''' ''' 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. ''' ''' What to match ''' What to look for ''' A list of Match/Group/Capture tuples 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 ''' ''' This method performs a pattern based substitution based on the provided input string, pattern ''' string, and replacement string. ''' ''' The source material ''' How to parse the source material ''' What the output should look like ''' _ 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 ''' ''' This struct is used to represent a Match/Group/Capture tuple. Instances of ''' this struct are created by the GetMatches method. ''' Friend Structure MatchResult ''' ''' Which match this is ''' Private _matchID As Integer Friend ReadOnly Property MatchID() As Integer Get Return Me._matchID End Get End Property ''' ''' Where the match starts in the input string ''' Private _matchIndex As Integer Friend ReadOnly Property MatchIndex() As Integer Get Return Me._matchIndex End Get End Property ''' ''' What string matched the pattern ''' Private _matchValue As String Friend ReadOnly Property MatchValue() As String Get Return Me._matchValue End Get End Property ''' ''' Which matching group this is ''' Private _groupID As Integer Friend ReadOnly Property GroupID() As Integer Get Return Me._groupID End Get End Property ''' ''' Where this group starts in the input string ''' Private _groupIndex As Integer Friend ReadOnly Property GroupIndex() As Integer Get Return Me._groupIndex End Get End Property ''' ''' What the group matched in the input string ''' Private _groupValue As String Friend ReadOnly Property GroupValue() As String Get Return Me._groupValue End Get End Property ''' ''' Where this capture starts in the input string ''' Private _captureIndex As Integer Friend ReadOnly Property CaptureIndex() As Integer Get Return Me._captureIndex End Get End Property ''' ''' What the capture matched in the input string ''' Private _captureValue As String Friend ReadOnly Property CaptureValue() As String Get Return Me._captureValue End Get End Property ''' ''' A convenient constructor which fills in all the fields contained in this struct. ''' ''' Which match this is ''' Where the match starts in the input string ''' What string matched the pattern ''' Which matching group this is ''' Where this group starts in the input string ''' What the group matched in the input string ''' Where this capture starts in the input string ''' What the capture matched in the input string 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 ''' ''' The streaming table-valued function used to split the string into a relation ''' ''' ''' _ 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 ''' ''' Don't allow callers to create instances of this class ''' Private Sub New() End Sub End Class ``` This is the [!INCLUDE[tsql](../../includes/tsql-md.md)] installation script (`Install.sql`), which deploys the assembly and creates the stored procedure in the database. ``` 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 ``` This is `test.sql`, which tests the sample by executing the functions. ```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 (?(?:[\w]+\s){3})jumped over the (?(?:[\w]+\s){2}(?:[\w]+))$', 'The ${dog} jumped over the ${fox}'); ``` The following [!INCLUDE[tsql](../../includes/tsql-md.md)] removes the assembly and functions from the database. ``` 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 ``` ## See Also [Usage Scenarios and Examples for Common Language Runtime (CLR) Integration](../../../2014/database-engine/dev-guide/usage-scenarios-and-examples-for-common-language-runtime-clr-integration.md)