Regular Expressions: How did I live without you?

Recently, I started a new position where I am writing much more SQL code than I ever have before.   In the past when I had a large amount of repetitive scripting to do I would use Excel to copy, concatenate and more.  I did not have extensive knowledge but could get the job done easily without too much typing.   It is a common fact that DBA’s don’t like to type or do anything twice if they have to.

My new favorite tool in Management Studio is Regular Expressions.  I use this now to replace what I have done in the past with Excel.  I have found it to be faster and far more flexible.

If you have not used Regular Expressions before, it is a kind of code to make your Find/Replace statements SING!  Regular Expressions are used in the same way as find/replace in Management Studio to replace groupings of content. You are able to create groups of information types to replace.

In Management Studio you use <CTRL> F for find and <CTRL>H for Replace.  To use Regular Expressions in Management Studio use the <CTRL>H and click the Find Options + button to expand the options and choose the Use:  box.  Choose Regular Expressions in the drop down box to expressly tell Management Studio to read the find statement as a Regular Expression and not just a string.

An example, of one of the many uses follows;

Select the values you want to contain in an insert statement.  Copy them to a new window.

Type <CTRL>h to bring up the find and replace window in Management Studio.   Use Regular Expression characters to classify strings and special characters that you want to replace.    Here I used the more common regular expressions that I think you will use often.  The ^ which is located above the 6 on the number bar is a start of line character.  The $ is an end of line character.  \t is one you may remember from csv file formats.  The {} are used to group items into a variable and will allow you to reference it later. The items in the {} are reference by the numeric order in which they occur.  In other words the first set of {} will be reference as \1 and the second as \2 and so on.

Here I have placed the regular expression beside the outcome so you can see how the expression is translated by Management Studio. 

This new format allows you to easily create an insert statement:

This is just one of many very useful things that can be done with Regular Expressions.

I have attached to this blog a word document, listing the Regular Expressions I got from Books on Line.  For the first few uses you may need the reference sheet, but you will quickly find that there are a few of them you use often and you will have them memorized in no time. 

Here is a quick reference to keep by your desk to help you build Regular Expressions:

http://technet.microsoft.com/en-us/library/ms174214.aspx