Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2018-06-18   |   Comments (3)   |   Related Tips: More > SQL Server Management Studio

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:

sql management studio

1. Format Text

We will first create a query like the following.

query

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:

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

query

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:

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:

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

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

\n$1$2 
where
query

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

remove

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

Use this for the Find:

^\s*--.*
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 (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 
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
\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.



Last Updated: 2018-06-18


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

Thanks!

Bob McC 


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

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

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
Result:
domain
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?


Learn more about SQL Server tools