SQL Is Your Friend . . . But CLR and RegEx Can Be, Too.

I have been learning, in "crash course" mode", about creating CLR UDF's this last week. I have known for some time now that SQL can't do everything . . . even if it can do an awful lot of things. ;-) However, working with CLR languages (in my case, C#) and building UDF's or SP's, while on my list, was not at the top of my list of things to do . . . however, that changed when I got a request to do something that I knew RegEx could handle.

Okay, so let's back up a bit and start from where I last left off. I recently changed positions where I work, which means I went from a developer/ad hoc DBA to (what I think is going to be called) a Developer DBA position. My job duties are now focused on SQL Server development. (By the way, that sound you hear in the background is me cheering. ;-) As a result, I am getting to more openly take on tasks such as helping developers with their SQL problems, developing Stored Procedures (SP's) and such, and developing SSIS packages. This then segues into the request I received to modify a stored proc so that it strips any nonnumeric characters out of the data it is inserting into certain columns in a table.

From a gig I did at Globalscape some time back, I remembered that the stripping of nonnumeric characters from a string is something that can be done quite easily using Regular Expressions (RegEx). In fact, I had done that very thing and remembered that the expression was one of the trivial, beginner lessons I had been given. (The RegEx is [^\d].) The only problem was that T-SQL doesn't do RegEx. However, C#does! And SQL Server 2005 lets you create CLR functions and stored procedures!

No problem! Right? Well . . . maybe. Now I had a plan . . . I just didn't have any solid information about how to carry out the plan. However, I had Google at my finger tips (so to speak ;-) and some books . . . so, I should be home free, right? Well, not exactly. Yes, I had a of information about how easy it is to write stored procedures (but usually not User Defined Functions); however, I didn't have what you might call clear instructions. The instructions were just a bit piece-meal. So, I set out on a quest to figure this problem out.

The first part, it would seem, would be to write the C# code for the stored procedure. This is where things started getting "entertaining." The instructions seemed simple enough:

Open Visual Studio;i

Start a new database project;

select a target database (which is a requirement of starting the project);

Add a new item of type User Defined Function

Now, that's a problem! You see, when I opened Visual Studio 2005 and got to the point of adding the new item of type UDF . . . there wasn't one. In VS 2005, while using C#, there isn't a UDF type object available for adding. there is one if you are working in VB.Net but not if you are working in C#! At this point, I turned to ITToolbox and posted a question on the C# forum 9some of you may have seen that exchange of emails). Everyone seemed to basically agree with me that there should be one or Microsoft should have one available for downloading from their MSDN support or someone out there in cyberspace should have one available. The only problem is that should means ought to but not necessarilywill. In other words, I looked everywhere I could think of and everywhere anyone else I contacted could think of and I found nothing.

I'll spare you the bouncing off of walls that I did as I searched for a solution to this but suffice it to say that there were many. However, I decided that I could use the stub of a C# UDF I found on-line by simply changing a couple of lines (so that it was specific to my task) and then following the commandline compile instructions that I also found on-line. Yup! That worked . . . so all I had to do was to get it installed as an Assembly.

Now, here's another interesting thing. There are several little steps that have to be made in order to install a CLR Assembly in a SQL Server 2005 database. Among other things, it would appear that one has to be logged onto the server itself based upon the "Not here you don't!" style of error messages I got when, I was trying to do it from my work station. Our Admin DBA and our Network Admin tried it from their workstations and got the same messages. To make a long story short, though, we did finally get the assembly installed on the target database. So, now, all I had to do was to create the UDF in the Functions area of Programmability, right? Well, sort of.

This is where I really got frustrated. In the process of adjusting things so that our Admin DBA could finally get the Assembly installed, we had uncovered a few problems that had been lurking in waiting for us on the server and instance I was trying to work with. However, even after getting those kinks worked out, the "simple" SQL command that you find everywhere n the internet and in every book that is supposed to create the actual UDF that will be used by T-SQL didn't work. Again, I was getting an error message that, when I Googled it, it seems everyone else was getting but not getting around. So, once again, I reverted to a technique I learned a long time ago . . . if what you are doing is failing consistently, find another way to do what you a trying to do.

After thinking about it, I suddenly had the insight that, if Microsoft's Visual Studio doesn't have the right template in the C# section in the 2005 version, maybe they fixed that in the 2008 version. So, I brought up VS2008 (Express) in the hopes of finding my lost template. Sure, enough, there it was! In the mean time, though, I had started a short list of additional RegEx functions that made sense, so I decided to add a couple of those to my experimentation. So, I quickly (mostly because the functions don't require much code at all ;-) wrote the set of functions that I wanted to install, putting each of them in separate C# assemblies within the same Partial Class.

To make a short story long (for a change), I compiled my project and, upon getting a clean compile (told you it wasn't much code ;-), I set about deploying my CLR UDF's to my local SQL Server 2005 Express instance. (I had decided to keep everything local as long as possible this time. ;-) The entirety of my efforts to accomplish my goal this time consisted of the coding, the compiling, and then clicking on the Deploy option for the code for each of the assemblies. Not only did it install the assemblies but it also created the UDF's in the Functions area. The last thing I had to do, though, was to edit each of the functions to make sure that the parameters that had been set up were set up correctly. (It seems that MS has gotten 99% of the kinks worked out in that process but the use of VarChar(MAX) parameters can confuse things. ;-)

So, at this point, I have created and deployed the UDF's to my local SSExpress database. I then tested each of them by passing several variations of RegEx expressions and strings to them. (That was how I originally found the gotcha with the VarChar(MAX) parameters.) However, that last bit (which took about 45 minutes, total, including the rewriting of the code!) happened yesterday (Friday) starting at 16:30 . . . so, by the time I was done, I was the only one in the department still there. On Monday, I plan on getting with our Admin DBA and seeing if it is as easy (heck, I'd settle for "almost as easy") to deploy the UDF's to the target database where I am trying to modify the stored procedure.

Also, my plan is to provide the C# code to anyone who would like to have it (once I clean up things just a tiny bit ;-). I plan to add a bit more documentation to the code before I make it available but that is about all. The code for the functions is not rocket science and is simply my own variation on previously existing RegEx functions that can be found all over the internet, so I see no reason to claim much more than the fun of having learned to create CLR UDF's. I would ask if someone gets my code and adds an interesting RegEx function, they would send it back with that source code included . . . and I'll provide that to anyone else with credit being given to those who have donated improvements. ;-)

Popular White Paper On This Topic

5 Comments

I have found that most on-line tutorials are rather like the cookbooks that are created and sold by various church women's groups. They are notorious for either providing a slightly inaccurate measurement on certain ingredients or omitting ingredients (or steps in the preparation) entirely. ;-)

Thanks Ralph for the insight you provide in your posts. Although much of your post topics are light-years beyond my experience, I find them interesting and thought provoking nonetheless. On a much simpler level, I'm relieved to find that I'm not the only one struggling with seemingly simple obstacles.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.

Over the years, I have found that many developers avoid learning about and using SQL until they absolutely have to, and then learn ...
more

Over the years, I have found that many developers avoid learning about and using SQL until they absolutely have to, and then learn and use as little as possible. I've also learned that SQL can not only significantly reduce the amount of code that needs to be developed but also the amount of time needed to get the job done (both coding time and run time). Thus my mantra, "SQL is your friend!"
After I became a Database Development, I progressively moved further away from applications development and toward developing databases, ETL processes, and such. However, I still believe, especially with regard to ETL processes, that SQL is your friend.
Now that I have moved into a "semi-retirement" mode where I am doing independent work and providing short term assistance to those who have database issues but don't have the budget for a full time Database Developer, I am also finding myself observing computer systems that I encounter and wearing my "System Analyst" hat as I consider how those systems show their highlights and their flaws. I also find myself observing all manner of developers for those same characteristics.
less

Receive the latest blog posts:

Share Your Perspective

Share your professional knowledge and experience with peers. Start a blog on Toolbox for IT today!