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:
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:
Test it!
These installation instructions are also commented in the downloadable C# program code.