Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

CLR UDT Declarations

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