SSMS Tip: Regular Expressions in SSMS

Regular expressions (or simply regex for short) have long been used by system administrators and data professionals for searching and manipulating text. Regular expressions allow the user to find, replace, and manipulate text based on the pattern they define in the expression. While every text editor allows simple search-and-replace capabilities, regex allows for searching for partial matches, using wildcards, and even integrating special characters (such as newlines and tabs) into the search or replacement text. Regular expressions in SSMS (SQL Server Management Studio) allow us to extend this behavior to T-SQL code modification as well.

In this brief tip, I’ll show you a couple of brief examples of the use of regular expressions for working with SQL code in Management Studio.

Regular Expressions in SSMS

When working with any text file (including SQL files) in SSMS, you can use the regular expression functionality to search or modify the text in those files. The inclusion of regular expressions in SQL Server Management Studio is very subtle, and is enabled by clicking a small icon on the search window.

Clicking the button circled above will process any regular expressions in the search box. Once enabled, you can use any of the numerous regex tools to look for patterns in your text files. Some of the common things you can use in a regex expression:

• \r: Find a carriage return character
• \n: Find a line feed character
• \s: Find a whitespace character
• \d: Find a digit
• +: Find multiple occurrences of the previous character. For example, using \s+ will match one or more spaces
• A-Z: Match any letter. You can do the same thing with a number, specifying only the numbers range you want to match
• {n1, n2}: Curly braces let you specify a minimum and maximum number of occurrences of a given match
• []: Brackets let you search for any one of the characters contained in the brackets. For example, searching for [\r\n] will match on either a carriage return OR a line feed. You can combine this with the + operator to look for more than one character in that defined class.

Let’s take a quick tour of what you can do with regular expressions in SSMS. First, let’s create some sample text.

In the above sample, I’ve used regular expressions in SSMS to create some variations of the phrase SSMS Tips. First, I want to show that using a simple regex how to match those variations. First, we’ll match the second variation which has a single space between the two words. Using the regex SSMS\sTips looks for a single literal space between those two words. The phrases matched in the regular expression are automatically highlighted in yellow.

As you’ll see, matching on the \s pattern finds not only the space character but also the tab, which is another whitespace character. If you want to match multiple occurrences of whitespace, you can use the + character after \s to indicate that one or more whitespace characters may be matched. This change results in also matching the phrase with two spaces between the words.

Want to match that row without a space between the words Use the curly braces qualifier to specify zero or more occurrences, such as SSMS\s{0,}Tips.

Now let’s make this a little more complex. I want to match SSMS at the beginning of the line, followed by a combination of whitespace, digits, or characters, then the word Tips. I’ll use the expression SSMS[\s\n\dA-Z]{0,}Tips, which looks for the following:

• The literal characters SSMS
• Zero or more ({0,}) occurrences of a space character, a newline, a digit, or a letter
• The literal characters Tips

Once you find the text you want by using the regular expression, you can replace it as you normally would. You can also use regular expression characters in the replacement text as well. One of the most common regular expressions I use is to replace a literal comma with the regular expression \r\n\t, which replaces each comma with a newline, carriage return, and tab, taking a single-line list (such as a list of column names) and breaking it into a more readable, one-line-per-item list.

Using regular expressions in SSMS takes a bit of practice, but once you get a feel for it, this trick can save you a lot of manual work.