Using Regular Expressions to Manipulate Text in SQL Server Management Studio 2017

By:   |   Comments (5)   |   Related: > SQL Server Management Studio


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.


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:

sql management studio

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.

long line of query

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

sql query

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

find and replace

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:


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


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:


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


The result will be:

my table

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:


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


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.

multiple blank lines

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

my table

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

current documents

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:

hello world

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

current document

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

Let’s say we have the following script

my table

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 ( because the schema name is already there.

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

In the Find box:


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

 $1 dbo.$2 
my table

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.

current document

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
. (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


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.


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 categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, August 11, 2022 - 9:20:12 PM - Jeff Moden Back To Top (90373)
I've not had much need for knowledge in this area except for things like \r, \n, \t, and the fact that \ is also used as "Escape"... until today... and the information is this great article made it all super simple! Thank you, Jeffrey, for the time you put into writing it, a really great order of revelation, and the perfect examples to go along with it!

Monday, May 20, 2019 - 2:14:23 PM - jeff_yao Back To Top (80135)

Hi @Bob McC, I never forget your excellent question, and I am now totoally convinced what you found was a bug in SSMS before 18.0.

Now with SSMS 18.0 released, I can confirm that this issue is gone.

Using your own example, if you want to replace "username" with "domain\timothy", all you need to do is like the following (under RegEx mode)

search "username"

replace with "domain\\timothy"

The \\ just means escape the charater \ and thus the RegEx engine will not treat \t in the RegEx context (i.e. a tab).

So in short, the bug is fixed in SSMS 18.0 but still exists in 17.9.1, and I have tested this in both SSMS.

Thursday, February 28, 2019 - 3:59:33 PM - Bob McC Back To Top (79164)

Thanks for your quick response; however I don't think I was clear in what my problem is.  There are no backslashes in the original text and it looks like you're using named groups in your example.  My focus is how to insert a backslash as part of the replacement text.

Let met give one more example to illustrate my point.

To keep it simple assume I have a script that contains: "EXEC sp_addrolemember N'db_datareader', N'username';"

and I want to replace "username" with "domain\timothy"

i.e. I want "EXEC sp_addrolemember N'db_datareader', N'username';" to be changed to "EXEC sp_addrolemember N'db_datareader', N'domain\timothy';"

How can I accomplish that with regular expressions turned on?* (I'm aware that I can do the above search and replace w/o regular expressions but It's only because I'm simplified it for this example.  In my real situation I'm doing a more complex search using regex, and when it comes time to replace characters I'm bumping into this situation.)


Bob McC 

Thursday, February 28, 2019 - 1:38:00 PM - jeff_yao Back To Top (79160)

Thanks @Bob for reading and commenting with interesting examples, really appreciate it.

Regarding Example 1 and 2, (they are basically the same)

Ex 1: 

Find: (\\t)

Replace: $1\

So user name Domain\Timothy will become Domain\T\imothy

Ex 2:

Find: (\\n)

Replace: $1\

So user name Domain\Neal will be Domain\N\eal

For example 3, that's even simpler

Find: \\

Replace: \\

So user name Domain\Neal will be Domain\\Neal

Hope this helps !

Wednesday, February 27, 2019 - 3:05:56 PM - Bob McC Back To Top (79146)

I frequently use Regular Expressions in SSMS; but it's definately quirky. 

My current problem I hope you can help with has to do with inserting a backslash to a string.

How do I insert a backslash when the character following the backslash is a valid escaped character?

Example 1: inserting a backslash followed by 't'
Search: username
Replace: domain\timothy
Result: domain imothy (with tab character between domain and imothy)  

Example 2: inserting a backslash followed by 'n'
Search: username
Replace: domain\neal
eal (new line inserted between domain and eal)  

So I tried the next logical thing and decided to use double backslashes, but that didn't work either...

Example 3: inserting a double backslash
Search: username
Replace: domain\\neal
Result: domain\\neal

Ugh! Any suggestions?

get free sql tips
agree to terms