Regex-based UPDATE for MS SQL Server 2005

Question

I need to do this for a whole DB column foo:

Regex regex = new Regex(@"^([A-Z][A-Z])(\d{6})$");
foo = regex.Replace(foo, "${1}0${2}"));

What is the simplest way to do this? I'm completely alien to MS SQL Server programming, and to C#, actually. Can I get the job done without preliminary 200 hours of CLR/MS SQL Server/T-SQL training?

Answer 1

It is possible to add regular expression support to a database in SQL Server 2005, but if you're doing a one-time processing of the data and have a tight schedule, you may be better of coding a simple client that does the conversion row by row (unless your data is truly huge.)

Even if it takes one hour to process all data, you'll get the job done and unless the database is being actively used during the conversion (which I wouldn't recommend no matter how you do it), you wouldn't see any side effects compared to any other method.

You can also have a go using a finished library for adding regular expression support to a database.