Just the other day, I had to perform a large text transformation on a database script. I had to rewrite a couple of hundred SQL statements like:

INSERT INTO MyTable ( 123, 'MyValue' )

... and reform these statements into something like:

UPDATE MyTable SET MySecondField = 'MyValue' WHERE MyFirstField = 123

Now this is where regular expressions come in handy. I just opened the script in SQL Server Management Studio, opened the Find and Replace window and checked the 'Use regular expressions' checkbox.

The quirky thing you need to keep in mind is that regular expressions in the 'Find and Replace' window have a bit of a different syntax than normal regular expressions you would write using the System.Text.RegularExpressions.Regex class of the .NET Framework. The transformation of the sample above could be achieved by using the following expressions:

Find: INSERT INTO MyTable \( {:z}, {:q} \)

Replace with: UPDATE MyTable SET MySecondField = \2 WHERE MyFirstField = \1

This syntax skipped the quoted strings that had double quotes within them, but for these one or two statements that I had to rewrite manually, it did a pretty good job. A real timesaver !

By David Stroobants, .Net Solutions Architect