Using Regular Expressions to Manipulate Text in SQL Server Management Studio 201...

By: Jeffrey Yao |  |   Related Tips:More > SQL Server Management Studio

"SQL Server in The Cloud" - click to register for this free webcast

Problem

I sometimes have to reformat complex query scripts, for example, I may want to replace multiple blank lines with one blank line or I want to make the FROM and WHERE clause in a new line, or what if I want to add the default schema name DBO in front of each table if the table is alone, i.e. not two/three/four-part named.

Solution

All the problems mentioned above can be easily solved with the supported Regular Expression (RegEx) feature in “find and replace” function.

However, before SQL Server Management Studio SSMS 2017 , SSMS had a very weird RegEx syntax as documented here . It is quite different, syntax-wise, from the commonly known .Net Regular Expression . I guess that is the major reason why the powerful feature is seldom used because RegEx is already difficult to master, making it different from the mainstream syntax and enhancing the confusion by sharing some of the same syntax yet for different a meaning make people less interested in using it, like the braces notation for example.

Now with SSMS 2017, the confusion no longer exists, and we can use RegEx in SSMS 2017 to do some impressive “find and replace” work.

My SSMS is the latest version 17.7 as of writing (2018/March/06), see below:

1. Format Text

We will first create a query like the following.

Assume our format requirement is to have a new line for “from” and “where”, i.e. the following format.

What we can do this in SSMS, go to menu Edit > Find and Replace > Quick Replace (or just press the Ctrl-H combination key).

Another window will pop up on the upper right corner and then click the 3 rd button on the bottom as shown below to use RegEx.

To format the query as required, i.e. FROM and WHERE will be in a new line, we will put the following RegEx in the [Find…] and [Replace…] boxes

In the Find box:

\s+from\s+

In the Replace box (note: there is a trailing blank space at the end):

\nfrom

Press the Replace All button, we will get the following text, i.e. the [from] is now in a new line.

Here is a quick explanation of RegEx in the [Find…] box: \s+from\s+

• \s : white space (including tab and blank space characters)
• + : this is a quantifier, meaning matching 1 or more times of the previous element
• from : this is the exact literal string
• \s : white space (including tab and blank space characters)
• + : this is a quantifier, meaning 1 or more of the previous object

In the Find box, “\s+from\s+” means one or more blank space followed by the literal string "from" and then followed by one or more blank space.

In the Replace box, “\nfrom “ means a new line followed by a literal string "from" followed by a blank space.

If we also want the [where] to be in a new line, we can do the same thing.

In the Find box:

\s+where\s+

In the Replace box (note: there is a trailing blank space at the end):

\nwhere

The result will be:

Actually, we can achieve the same result with one time find and replace operation. We will put different RegEx in [Find…] and [Replace…] as follows:

In the Find box:

\s+(from)|(where)\s+

In the Replace box (note: there is a trailing blank space at the end):

\n$1$2

2. Remove Multiple Blank Lines

If I open a script and see multiple blank lines like the following, I want to clean up the blank lines.

So I can put the following in the “find & replace” boxes:

Then click “Replace All” (the highlighted button above), we will get the following expected result:

Note: In [Replace…] box, there is nothing there, so we can remove all found strings.

Here is a quick explanation of the RegEx string [^\s*\n] in the [Find…] box.

• ^: this is an anchor (i.e. position indicator), it means the match must start at the beginning of the string or line.
• \s: white space (can be tab as well)
• *: this is a quantifier, meaning match 0 or more times of the previous item
• \n: new line

3. Remove comment lines

We can fill the [Find…] box with the following RegEx string and leave [Replace…] box as empty.

Use this for the Find:

^\s*--.*

After Replace All action, the result is the following. Then we could also remove the blank lines like we did above.

4. Add dbo. in front of one part named object after [from] or [join]

Let’s say we have the following script

We can see that line 3, 13, 14 all have tables without schema name, let’s assume the default schema for these tables is [dbo], so I want to add [dbo.] in front of such objects. But I do not need to add any schema to line 7 (sys.objects) or line 10 (mydb.abc.mytable) because the schema name is already there.

We can fill the following RegEx inside [Find…] and [Replace…] boxes

In the Find box:

\s*(from|join)\s+([^\.\s]+)\s+?

In the Replace box (note: there is space at the beginning and the end):

 $1 dbo.$2

After run the Replace All action, we get the following result, and we can see we have dbo. added in front of original one-part table names.

There can be many other scenarios like adding a column name for a specific table, or replacing names, stripping off double quotes, etc.

Key RegEx Elements

RegEx is pretty daunting for new learners, so I’d recommend the following key elements that I feel are frequently used and can actually solve most of issues I encountered.

Quantifier: RegEx has basically the four quantifiers

Quantifier Meaning Example * 0 or more times of the previous element \s*, no space or more spaces + 1 or more times of the previous element \s+, one space or more spaces {n} Exact n times of the previous element \d{3}, three digits {m, n} Between m and n times of the previous elements. If n is omitted, then at least m times \d{1,3}, either one or 2 or 3 digits \d {2, } at least two digits

Special Characters

Character Meaning \s white space (including tabs) \d Decimal digits, i.e. 0 to 9 \w Alphanumeric [a-zA-Z0-9] \n New line \r . (dot) Any character

Characters that need to be escaped

There are total 11 characters that if you want their literal meaning, they should be escaped by using a back slash (\), they are

$ ^ { [ ( | ) * + ?

So if I want to find a pattern like $123.45 in a string, my RegEx should be \$\d+\.\d{2}

Anchors - The most important two anchors are:

Anchor Meaning ^ Beginning of a string $ End of a string / line

Others

Another two important elements for Find and Replace are grouping constructs and substitution.

The grouping construct syntax is: (subexpression)

The substitution syntax is: $n, where n=1,2,3,…

The logic here is if RegEx matches a subexpression inside a grouping construct, its exact value is stored in its corresponding substitution, such as $1. If you have five grouping constructs, you will have 5 substitutions starting from $1 to $5.

This is extremely useful in Find and Replace, in the above example 4 (i.e. adding schema dbo), I used grouping constructs to find the word [from] or [join] and the one part named table, and then replaced them with [from] or [join] unchanged, i.e. $1 and then added ‘dbo.’ in front of the one part-named table $2.

Summary

RegEx is a powerful and efficient tool to manipulate strings and text. In SSMS 2017, the common . Net RegEx syntax replaces the old SSMS RegEx syntax seen in pre-SSMS 2017 versions.

In this tip, we have demonstrated a few examples about using RegEx to do the Find and Replace function in SSMS, we can use RegEx to do formatting and finding accurate strings to our needs.

Of course, RegEx learning and practice is not a one day or one week exercise, but a few Google searches plus an understanding of why a RegEx is written in that way will surely improve your skills.

Next Steps

There are quite a few RegEx tips on this website, they can be good tutorials for your understanding of RegEx. Since those RegEx are the standard .Net RegEx syntax, they can be used directly in current SSMS 2017.

• SQL Server Regular Expressions for Data Validation and Cleanup
• Using Regular Expressions to Find Special Characters with T-SQL
• SQL Server function to validate email addresses
• RegEx-Based Finding and Replacing of Text in SSMS

Last Update: 2018-06-18

About the author