Chapter 21. Using regular expressions in SSMS

John Paul Cook

Regular expressions are known for their power and their cryptic nature. Many people avoid using them because the value isn’t perceived to be worth the cost of learning an arcane syntax. But there are a few high-value regular expression edits that have simple syntax. SQL Server Management Studio (SSMS) supports the use of regular expressions when editing queries. You can reap the benefit of regular expressions without having to master the tedious subtleties of advanced regular expression syntax. This chapter shows you how to efficiently use a few simple regular expressions within SSMS.

Eliminating blank lines

When you’re copying a query from an email message or web page, superfluous blank lines may appear after you paste the query into SSMS. Manually removing the blank lines by deleting them one at a time is both tedious and error prone. A simple global edit using regular expressions can safely and efficiently remove the unwanted blank lines with a minimum of effort. Here’s an example of an emailed query that has unwanted blank lines in SSMS:

Saving the query to a file and examining the file in a hex editor reveals the root cause of the problem, as you can see in figure 1.

Removing extra newline characters

Collapsing multiple lines into a single line

Using the beginning-of-line metacharacter

Using the end-of-line metacharacter

Summary