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