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.
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):
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:
Create the assembly object in SQL Server:
Create a function-prototype for each function in the assmebly that you want to publish:
These installation instructions are also commented in the downloadable C# program code.