SQL Server 2008 RegEx CLR Use

Question

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/magazine/cc163473.aspx , but I don't know what to do with them.

So I have 2 questions. The regex is only used when importing records (up to 1,000,000 at a time, and up to 50,000,000 a year) against 2 fields.

1st question. I can do it manually 1 record at a time in my code (ASP.NET), and I know it would be a performance hit, but no idea how big of one. Any input on this?

2nd question. Can someone point me to a simple step by step instruction on how to install the CLR files I downloaded? I tried doing a search, but that either didn't show the info I needed, or was too buried in other info for me to find it.

Thanks

P.S. I'm running SS 2008 (not R2) and VS.NET 2008.

MSDN has info on how to deploy CLR dlls from t-sql: msdn.microsoft.com/en-us/library/ms345099.aspx... e.g. CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE; — Charleh
– Charleh, Commented May 17, 2012 at 18:05
You don't say from WHAT you import your records? From another database? From a file? If from a file, what is its format? — David Brabant
– David Brabant, Commented May 17, 2012 at 18:26
Import from a flat file (csv). Need to remove all non-alphanumeric chars from 2 fields. — Tom Collins
– Tom Collins, Commented May 17, 2012 at 22:50
I think it would be better to transform that file before importing it then. — David Brabant
– David Brabant, Commented May 18, 2012 at 5:35

David Brabant · Accepted Answer · 2012-05-17 18:12:16Z

1

See the article here for a complete tutorial.

answered May 17, 2012 at 18:12

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Collins Over a year ago

Started reading it, but got distracted with something else. Looks like it will cover what I need.

paparazzo · Accepted Answer · 2012-05-18 01:20:02Z

0

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.

edited May 18, 2012 at 1:20

answered May 17, 2012 at 18:21

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

1 Comment

Tom Collins Over a year ago

Do you see much of a performance difference then? One million update commands sent to the DB, or one update command that uses the CLR function? If the hit is slight, then the ASP.NET option is more attractive since I don't have to require my client to install the functions as well.

Collectives™ on Stack Overflow

SQL Server 2008 RegEx CLR Use

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related