Sql Server 2008 Regex Clr Use

OK, I realize that SS 2008 doesn't have native regex functions. I also see that it's possible to add them using CLR. I found the regex CLR at http://msdn.microsoft.com/en-us/magazi

Solution 1:

See the article here for a complete tutorial.

Solution 2:

If you only need REGEX for a dataload then you could do it one record at a time in ASP.NET. Even with SQL CLR integration you would be doing it one record at a time as SQL would pass the value one records at a time. If you want to use REGEX as a condition in the where clause then SQL CLR integration would be the only option.

What I am doing is parsing one line at a time. One line will result in 1 - 5 SQL inserts. Then I do those SQL inserts asynch. If the next parse completes before the insert I wait. In my case it is about a dead heat so I get parallel processing. If SQL calls regex you are limited to serial processing.

I parse and load data a lot. If you are concerned about speed look more at the SQL side than parse. I go so far as to disable SQL indexes parse and load 10 million records and then rebuild indexes. Regex a line at time is typically faster than SQL insert unless the SQL table has no indexes.