Latest News

Popular

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]
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.