concept regular expression in category sql

This is an excerpt from Manning's book SQL Server MVP Deep Dives Vol. 2. Login to get full access to this book.

Figure 2. Before (top) and after (bottom) view of a query. A regular expression removed the blank lines. Notice that the Use check box is checked and that Regular Expressions is selected.

As a matter of style, a SELECT statement may have one column per line or multiple columns in the same line. Changing from one style to another is easy to do with regular expressions. In our example query, the second, third, and fourth lines of the query begin with six space characters followed by a comma. The second, third, and fourth lines are preceded by a \n. You can use this knowledge to define a pattern. As a regular expression, this pattern must begin with a \n. By replacing \n , (\n followed by six spaces and a comma) with a single comma, newline characters and the six leading spaces are edited out, causing the first four lines to become one line (see figure 3).

Figure 3. A regular expression collapsed the first four lines (query shown on top) into a single line (query shown on bottom).

Regular expressions are useful in turning a list of column names into a SELECT statement. You can quickly add a comma to either the beginning or the end of a selection of lines by using the appropriate metacharacter. In these examples, the changes are applied only to the selection instead of the entire file to prevent insertion of characters where they aren’t wanted.

In this chapter I’ll discuss the use of regular expressions in extracting information from texts using SQL Server 2008 (most of this chapter also applies to SQL Server 2005) and the Microsoft .NET Framework implementation of regular expressions.