HOEKSTRA.CO.UK

The task of matching strings between heterogeneous systems, especially that of matching personal or company names or addresses, is not easy with SQL Server's limited set of built-in string SQL functions. Here is a C# CLR-Assembly (with source code) for SQL Server with some advanced string-handling functions that may help:

  • LTrim - like Oracle's LTRIM function.

  • InitCap - like Oracle's INITCAP function

  • FlattenCharSet - Replace western characters with diacritics with best-choice, non-diacritic characters

  • StripPunctuationMarks - Remove all punctiation marks from the given string.

Example:

SELECT dbo.StripPunctuationMarks('Pharma A.G. (CH)')
PharmaAGCH

The CLR Assembly is written in C# in Visual Studio 2008 and works on SQL Server 2005 and SQL Server 2008 and you can download it here . Included is a pre-build assembly, SSISUtils.dll.

How to install a CLR-Assembly on SQL Server

You need to "soften up" your SQL Server's security with this command (as sysdba):

USE master
ALTER DATABASE master SET trustworthy ON

Now copy the 'assembly' (i.e. the .dll file) to a sensible place on your SQL Server machine, like C:\ETL\CLR. 

Select your database and enable CLR usage:

USE [mydatabase_name]
sp_configure 'clr enabled', 1
reconfigure

Create the assembly object in SQL Server:

CREATE ASSEMBLY [SSISUtils]
AUTHORIZATION [dbo]
FROM 'C:\ETL\CLR\SSISUtils.dll'
WITH PERMISSION_SET = SAFE

Create a function prototype for each function in the assembly that you want to publish:

CREATE FUNCTION [dbo].[LTRIM]( @input nvarchar(100), @trimchar nchar) returns nvarchar(100)
WITH EXECUTE AS caller AS
external name [SSISUtils].[SSISUtils.StringUtils].[LTRIM]
go
CREATE FUNCTION [dbo].INITCAP( @input nvarchar(100)) returns nvarchar(100)
WITH EXECUTE AS caller AS
external name [SSISUtils].[SSISUtils.StringUtils].INITCAP
go
CREATE FUNCTION [dbo].FlattenCharSet( @input nvarchar(100)) returns nvarchar(100)
WITH EXECUTE AS caller AS
external name [SSISUtils].[SSISUtils.StringUtils].FlattenCharSet
go
CREATE FUNCTION [dbo].StripPunctuationMarks( @input nvarchar(100)) returns nvarchar(100)
WITH EXECUTE AS caller AS
external name [SSISUtils].[SSISUtils.StringUtils].StripPunctuationMarks
go

Test it!

DECLARE @a VARCHAR(20)
exec @a = INITCAP  N'heLo wOrld'
print @a
HeLo WOrld

These installation instructions are also commented in the downloadable C# program code.