Regular Expressions within a Crystal Report based on SQL Server

[nscheaffer]

Yesterday I responded to a discussion seeking help to replace numbers with asterisks.

While I think my suggestion to loop through the string of data one character at a time replacing each character with an asterisk if it is a number will work I kept thinking that it could be improved.  I am certain the code I provided could be better, but it seemed to me that a whole different approach using a regular expression would be even better.

What I am going to suggest here requires some basic C# programming skills (which I have, but are rusty from lack of use) and control of your database (or at least a helpful DBA).

About six weeks ago my manager (and helpful DBA) asked me to help him create a CLR-based, user-defined function to assist in analyzing SQL Server 2008 trace data.  He had found a CLR function created by Itzik Ben-Gan and detailed in his book Inside Microsoft SQL Server 2005: T-SQL Querying.  Fortunately I had a copy of that very book updated for SQL Server 2008 on my desk to guide me through the details (pages 160 -162).

So my first step was to create a C# class library project.  I used Visual Studio 2010 and compiled my code targeting .NET 3.5 since the was the newest version of the .NET framework that was on the SQL Server to which were going to be deploying this function.

Here is entirety of the code taken directly from Ben-Gan's book...

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.Text.RegularExpressions;

public partial class RegExp

{

    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]

    public static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement)

    {

        return (SqlString)Regex.Replace(input.Value, pattern.Value, replacement.Value);

    }

}

Proceeding to follow the steps laid out in the book you have to enable CLR on your SQL Server, load the IL code and register the RegexReplace function.  That all worked like a charm.  My manager got what he needed and we forgot about it.

Then I came across the discussion mentioned above and wondered if I could use that RegexReplace function when using a command object in Crystal Reports.  You sure can.

For the purposes of this example I created a global temporary table and inserted 3 rows as follows...

CREATE TABLE ##MyTable (ID INT, Column1 VARCHAR(50))

INSERT INTO ##MyTable (ID, Column1) VALUES (1, 'Some data $12,345.78')

INSERT INTO ##MyTable (ID, Column1) VALUES (2, 'Some other data 01/31/2013')

INSERT INTO ##MyTable (ID, Column1) VALUES (3, '123 Main Ave. - Apt #2B')

I then created a Crystal Report based on a command object and used my RegexReplace function.



And here are the results.



I am not a regular expression expert; there are plenty of other in-depth resources elsewhere.  My only intent was to show this was possible, not to incorporate comprehensive regular expression support into Crystal Reports.  If you would like to see that, vote for my idea. Update: This suggestion has been marked at "Not Planned".

Please understand that I did this as a proof-of-concept.  I don't have a need for this right now, but since I figured this much out I thought I would share it and perhaps this could be a building block for something for someone else.  The C# code could be enhanced to expose more regular expression  functionality or perhaps you could implement something totally different  that can be done in C#, but not in SQL Server or Crystal Reports.

Enjoy,

Noel