String-Matching help for SQL Server

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 be 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 assmebly 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]

CREATE FUNCTION [dbo].INITCAP( @input nvarchar(100)) returns nvarchar(100)

WITH EXECUTE AS caller AS

external name [SSISUtils].[SSISUtils.StringUtils].INITCAP

CREATE FUNCTION [dbo].FlattenCharSet( @input nvarchar(100)) returns nvarchar(100)

WITH EXECUTE AS caller AS

external name [SSISUtils].[SSISUtils.StringUtils].FlattenCharSet

CREATE FUNCTION [dbo].StripPunctuationMarks( @input nvarchar(100)) returns nvarchar(100)

WITH EXECUTE AS caller AS

external name [SSISUtils].[SSISUtils.StringUtils].StripPunctuationMarks

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.