SQL User Defined Types CLR - String
User Defined Types CLR SQL - String
This article is a continuation of the SQL User Defined Types CLR I. It describes implementation of a CLR User Defined Type with a String (a non-blittable type). The UDT accepts the string as input (in 1 of the constructors) and replaces the non-alphanumeric characters with a space using a C# RegEx.Replace function.
Getting Started with SQL CLR User Defined String Type
As this CLR UDT uses a string (or varchar SQL type), the struct will need to implement the IBinarySerialize interface and will use Format.UserDefined with a MaxByteSize of 8000.
Create the variables and 2 constructors; one for the IsNull condition and one for the variable.
private SqlString _mValue;
private bool m_Null;
public MyString(bool isnull)
{
m_Null = isnull;
_mValue = null;
}
public MyString(string s)
{
_mValue = s;
m_Null = false;
}
Create code to override the ToString function in the UDT.
public override string ToString()
{
return m_Null ? null : _mValue.ToString();
}
Add code for the IsNull property and Null condition for the string.
public bool IsNull
{
get
{
return m_Null;
}
}
public static MyString Null
{
get
{
MyString h = new MyString();
h.m_Null = true;
return h;
}
}
Create code for the Parse method that includes Regex.Replace to remove all non alphanumeric characters from a string.
public static MyString Parse(SqlString s)
{
string value = s.Value.ToString();
if (s.IsNull)
return Null;
//remove the non alpha numeric characters from the string
value = Regex.Replace(value, "[^A-Za-z0-9]", " ");
//additional Regex to remove multiple white spaces from the string
Regex r = new Regex(@"\s+");
//replace the multiple white spaces with a single space
string v2 = r.Replace(value, @" ");
MyString u = new MyString(v2);
return u;
}
Implement the IBinarySerialize interface with the Read and Write methods for the BinaryReader and Binary Writer.
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
_mValue = r.ReadString();
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(_mValue.ToString());
}
#endregion
Create and Test the CLR SQL User Defined Type in SSMS
Build the assembly and in SQL Server (SSMS) create the assembly and the type.
CREATE ASSEMBLY MyNum
FROM
'C:\Dev\SqlUDT\SqlUDT\bin\Debug\SqlUDT.dll'
GO
CREATE TYPE myString
EXTERNAL NAME MyNum.MyString
GO
Test the UDT with T-SQL code
declare @s myString
Set @s = 'stuff*&^111'
SELECT @s.ToString()
stuff 111
(1 row(s) affected)
Note: The RegEx parsing technique used in this article may have also been applied to a different article related to the removal of special characters from a string: SQL Server Data and Overnight Mailing List Formatting