Click here to monitor SSC
  • Av rating:
  • Total votes: 35
  • Total comments: 5
Phil Factor

RegEx-Based Finding and Replacing of Text in SSMS

14 January 2011

So often, one sees developers doing repetitive coding in SQL Server Management Studio or Visual Studio that could be made much quicker and easier by using the Regular-Expression-based Find/Replace functionality. It is understandable, since the syntax is odd and some features are missing, but it is still worth knowing about.

The Find/Replace feature of SQL Server Management Studio (SSMS) supports Regular Expressions. ‘Nice,’ you will think ’This will be very handy for those refactoring jobs that would otherwise require programmers' editor.’ Because you know that SSMS is a .NET application, you'll imagine that it uses the lovely Regular Expression library that is within .NET. Actually, it doesn't. It uses a quirky implementation of regular expressions, with unusual syntax, and few advanced features.

Here it is being used in SSMS.

It is Microsoft’s standard  RegEx implementation for Find/Replace in its applications. It comes from way back before Microsoft paid so much heed to industry standards. The same code is used in Visual Studio, even the completely re-written VS2010. It is very oddball syntax, and comes originally from Visual C++ 2.0. For some reason it was also used in Microsoft FrontPage and Expression Web too. The determination of Microsoft to maintain compatibility with Visual C++ Studio is one of the abiding mysteries of Windows. We were promised a change in VS2005, but it never happened. It has reappeared in VS2010, and so is likely to be there once again in the replacement for SSMS.

So is it any use?

Even though it is odd, and lacks some of the features one expects, it is still very handy, particularly if you have a few RegEx favourites squirreled away. Although it is definitely possible to type RegEx strings directly into the Find Box and have them work perfectly, a library of RegEx strings can save a lot of time for routine tidying up of SQL code, or formatting the results. Formatting results? Yes, the results in the result pane can quickly be turned into HTML or SQL Insertion code using a RegEx search and replace, though this is often easiest done in a more specialised programmers' text editor.

The oddest quirk of the find/Replace Regex is the use of curly brackets to denote a capturing group. (e.g. {1}{2} ) Microsoft confusingly calls these 'tagged groups'. You'll notice that minimum and maximum ranges aren't supported for quantifiers; there is no positive look-ahead or look-behind assertion. There seem to be no mode modifiers. There are a whole lot of character classes that are non-standard and some extra ones. For example ‘:q’ matches both 'single' and "double" quotes, ‘:i‘ matches C/C++ identifiers. Hmm. Not much help to SQL coders.  You can get around some of these problems. 'Prevent match' can be used as a poor-man's negative look-ahead and negative look-behind. (you can do a sort of positive look-around too) You can specify ranges, but only if they are very narrow, via a tortuous work-around where you have to specify every valid count.

Another quirk of the RegEx ’Find’ is that its method of traversing is different from a ‘Replace All’ in that it will Find strings that are part of strings that have previously been found: By contrast, it will never replace part of a string that has already been replaced. The Find Next button will search character-by-character from the current caret location rather than walk in the same way as a RegEx which will proceed from the end of the previous match, not counting the look around. This has caused a lot of confusion.

Why should these incompatibilities matter? Surely, it is just a matter of learning the new dialect? Well, when one is working in different dialects of RegEx, it makes life doubly awkward. I find that I have to constantly look up the equivalents in a chart to make progress in creating RegEx strings. Imagine having to do a lot of RegEx work using the .NET RegEx library, or JavaScript, and then have to use a different dialect of RegEx when searching for, or replacing stuff!

Find and Replace Regular Expression Examples

Before we start explaining RegEx in detail, let's run through a few typical Search and Replace operations that can use regular expressions.

To take out blank lines (as when you import code from Firefox sometimes) find

\n @\n 

… and replace with


To find all instances of INT that aren't commented out  try


(this is an example of a good use for a negative look-behind to check that the line isn’t commented)

To select up to the first hundred characters in a line


This will try to select 100 characters from the start of the line, and only if it fails, it will select all of them

To insert a string at a particular column position


And replace with..

\1'I've inserted this'

(or whatever!) This simply matches a line with 100 characters  and replaces the first hundred characters with the first hundred characters plus the string you want to insert.

To delete ten columns after column 100 use this


Replace with


What would happen if you used  \2\1? Yeah. Useful once in a while!

To find either a quoted string or a delimited string, use


To replace all quoted strings with quoted delimiters, find


… and replace with


This shows how to create a capturing group using {},  and use it in the replace expression. It can also be used in the find expression

To remove inline comments that take the whole line,  find


… and replace with nothing

Here, we are using a greedy quantifier * to find the entire comment  line (line starting with --)

To find any valid object name with delimiters


to take out the quoted delimiters [ and ] where they aren't necessary, replace with


This illustrates the use of character class definitions to determine whether the delimited strings contains only characters that are valid in a SQL identifier.

To find 'tibbling' (use of tbl, vw, fn, or usp prefixes) use this


Here we show one of the most useful of constructs, where alternative strings to search for are listed. It also shows how the special ‘<’and ‘>’ characters are used to delimit the start and end of a word.

to de-tibblize code, us this


… and replace with …


Here we add a capturing group (Microsoft calls them tags) to capture the word without the tibblizing prefix. (add your own to taste!)

to match any word at least 3 characters long, you can use  


This is one of the workarounds for the lack of proper range quantifying. It is the equivalent of  {n,} in normal RegEx

to find multi-line comments using /* */ use this


Normally, RegEx strings will stop searching at the end of the line if you use the standard wildcard. This RegEx uses a trick to get around that.

To find a Title-cased word (word starting with a capital letter followed by lowercase)


Microsoft have some convenient shorthand characters to represent character classes. Here we illustrate their use with the :Lu:LI

to take out the headers that SMO puts in like
/****** Object:  StoredProcedure [dbo].[uspGetBillOfMaterials]    Script Date: 01/07/2011 19:03:05 ******/
find this,


replace with nothing

This shows the simplest quantifier. We use the [\*]^6 to represent six stars ******

to comment out lines, select the lines, Make sure you have ‘Look in selection’ and  find


 replace with


to un-comment out lines, select the lines, Make sure you have ‘Look in selection’ and  find


replace with


To find two words separated by up to three words (in this case FROM and AS) use


With normal syntax, you’d use \b FROM (?:\W+\w+){1,3}?\W+AS\b but we have no range quantifiers, so we are forced to use the ascending alternatives (descending if we want to be greedy rather than lazy). This becomes ridiculous if we want to specify the quantifiers for a complex expression. We’d have to duplicate the long expression.

To find the first  object that is referenced by a FROM clause (doesn't successfully avoid strings or multiline comments), use


here you have something that is looking for a whole lot of different alternative keywords merely by grouping them and using the | character to .

To find either an integer or a  floating point number, one can use the following RegEx which is a bit long but simple in structure


This starts at a word boundary that is not preceded by a dot. It first looks for a floating point number in exponential notation. then it looks for a number in conventional notation before finally trying for an integer.

The negative lookahead and negative lookbehind

The idea of look-ahead and look-behind is slightly hard to understand.  The object of the mechanism is to do be able to specify what shouldn’t or should be next to the string you are trying to match, without including the characters you look at in the match. If you are ‘replacing all’, you may want to include what you’ve looked at ahead in the next match. You could well want to look behind at things that have already been inspected in a RegEx for a match.  Using the Find/Replace RegEx syntax, we can get somewhere close to a ‘negative’ check with a ‘prevent match’; in other words we can specify what should not precede, or follow, the match, but we can’t say what should  do so, even with a double negative.  If, for example, you are searching for words in the AdventureWorks database, you’ll have the word HumanResources appearing a great deal.

~(Human)Resources  finds the word 'Resources', but not when immediately preceded by 'human' (negative lookbehind)

Human~(Resources)  finds the word Human, but not when immediately followed by 'resources' (negative lookbehind)

~(:b)Resources     finds the word Resources when not preceded by whitespace

but the expression cannot, it seems, be persuaded to find a positive lookaround.  There are plenty of times that you’d want this. An example is using RegEx strings to tidy up a block of DECLARE statements into a single list, or if you want to select  a range of characters  based on column number

Constructing a RegEx for SSMS or VS search/Replace

I find the best approach to constructing a RegEx is to create some test data first that will thoroughly exercise the RegEx you want to create. I create a new query window, and rely on the history feature of the ‘Find What’ combo box and the ‘aide Memoir’ window reached by the arrow key to the right-hand side of it.  

Start simple, and don’t be afraid of jettisoning what you try in favor of a different approach. Whatever you try is kept so you can return to it (I use AceText as well, so as to give me a long-term memory of what I’ve tried.) Build up the RegEx strings gradually, trying them out with every iteration. After a while, you will be able to ‘read’ RegEx strings as if you were using a RegEx IDE such as RegExBuddy (I’d use that if only it understood the quirky syntax of Microsoft’s search/replace RegEx.) Keep your old RegEx strings for re-use. RegEx strings are one of those snippets that are well-worth keeping.

Don’t be tempted to be clever with RegEx strings.  I’m quite content to take a simple lumbering approach to the way RegEx strings work, though it may not impress many bearded developers in baggy jumpers, bottle glasses and sandals.

Taking things in stages.

For more complex tasks such as marking up code to colorize it, for creating tables from results, or for creating insert statements, it is often quicker and more maintainable to run several RegEx replacements in a particular order to achieve what you want.  Let’s take a worked example.

Imagine we want to turn a result into a SELECT  statement that returns a table. (it could use multi-row VALUES though there is a row limit) Our example will be short, but you’re likely to tackle much bigger results that are impossible to do by hand.

In AdventureWorks, open a query window. Set the Query -> Results to -> Text. Click on the Query -> ‘Query options…’, Click on Results Text in the tree on the left and set the dropdown box at the top right of the form to Tab Delimited. Click on the ‘include column headers in the result set’ optionbox so that it is UNTICKED.

Run this…

SELECT  ErrorLogID, ErrorTime, UserName, ErrorNumber, ErrorSeverity,

        ErrorState, ErrorProcedure, ErrorLine

FROM    ErrorLog

Click on the result pane to get focus

Find What

Replace With



Take out all the rows that aren’t part of the result (rows without tabs)



replace all tabs,  with the ‘,’ string, as well as at the start and end of a line



Strip off unnecessary quotes around numbers



Put the SQL statement you want at the start of the string


Finish off the end of each line by deleting the last ,’

Then nick out the initial ‘UNION’, and you are left with…

SELECT 1,'2010-07-09 09:42:34.853','dbo',547,16,0,'uspUpdateEmployeeLogin',15

UNION SELECT 2,'2010-07-09 09:42:55.497','dbo',547,16,0,'uspUpdateEmployeeLogin',15

UNION SELECT 3,'2010-07-09 09:58:31.120','dbo',547,16,0,'uspUpdateEmployeeLogin',15

UNION SELECT 4,'2010-07-09 12:12:20.757','dbo',547,16,0,'uspUpdateEmployeeLogin',15

UNION SELECT 5,'2010-07-10 22:30:41.567','dbo',547,16,0,'uspUpdateEmployeeLogin',15

Of course, if you have strings with single-quote marks in, you’ll have to double them by replacing ' with '', but otherwise, you’re done.

Feature Comparison with standard RegEx


The same as standard RegEx
different symbol or syntax;
Missing from Search/Replace

Here we list out the components of the Find/Replace Regex and compare each with the equivalent in standard Regex. If the two are the same, the background is in white. If the tw are different but the functionality is pretty well the same then the background is light gray, otherwise it is silver (see the key on the left)





Metacharacters outside square brackets



\n means end of line, \\ means backslash

general escape character (the next character as either a special character, a literal, a backreference, or an octal escape.) only [\^$.|?*+() have special meanings and have to be escaped


^-- finds a SQL comment line

assert start of string (or line, in multiline mode)



GO$ finds GO at the end of a line

assert (anchors) to end of string (or line, in multiline mode)



^.* finds the next line

match any character except newline (by default)



[0-9] specifies any character between 0 and 9

start character class definition These definitions are by default case sensitive



[a-z@]  (any character between a to z or a ")

End character class definition



<(outer|cross)>[^:a]#<apply> (finds all instances of OUTER or CROSS APPLY.

start of alternative branch (the OR or PIPI  operator)




start sub-pattern



(ON|OFF)   (finds either 'ON' or 'OFF')

end sub-pattern


Extends the meaning of ( - is also used as a quantifier



0 or 1 .  Can also mean 'quantifier minimizer' Also used after a bracket to extend its meaning



0 or more -greedy



1 or more -greedy (also "possessive quantifier")


lazy zero or one



favo[u]@rite matches both  favorite and favourite (and favouuurite)

lazy zero or more (matches as few as possible)



lazy one or more



[\@]^2 (matches @@Identity but not @identity)

N is a positive integer. Matches exactly n times.


N is positive integer. Matches at least n times.


Range specifier. M and n are positive integers, where n <= m. Matches at least n and at most m times.


When this character immediately follows any of the other quantifiers (*, +, ?, {n}, {n,}, {n,m}), this specifies that the matching pattern is lazy, in that it matches as little of the searched string as possible. Otherwise it matches as many as poassible.



Wildcard: . Matches any single character except "\n".

Metacharacters inside  square brackets  (In a character class )



[\\\*]  (either a backslash or star)

general escape character



[^\@\#]  (not an ampersand or hash char)

negate the class, but only if the first character



[0-9A-F] (valid hex digit

indicates character range



End character class definition



POSIX character class

Non-Printing characters in strings



Match a backspace



 Match a tab character


 Match a carriage return character Equivalent to \x0d and \cM.



 Match a line feed character Equivalent to \x0a and \cJ.



Match a bell character



Match a escape character


Match a form feed character  Equivalent to \x0c and \cL.


Match a vertical tab character



\xD8 (0xD8 - 216 decimal)

Match the ASCII or ANSI character with position  in the character set (Hexadecimal escape values must be exactly two digits long).



 \u0106  (U+0106  in unicode table)

Match the Unicode character that occupies code point in the Unicode character table

Generic Character types (character classes)



Match any single character that is not a line break character



\@:a* finds next variable

 Match a single character that is a “word character” (letters, digits, and underscores) Equivalent to '[A-Za-z0-9_]'.



<SET>[^:a]*[:a]*[^:a]*<(ON|OFF)>  (finds all SET xxx On or OFF statements)

Match a single character that is a “non-word character” c.f. [^A-Za-z0-9_]



level:dname (finds Level0name, levl1name etc)

Match a single digit 0..9



Match a single character that is not a digit 0..9



Match a single character that is a “whitespace character” (spaces, tabs, and line breaks)  Equivalent to [ \f\n\r\t\v].



Match a single character that is a “non-whitespace character”


 Match a single character in the range between “0” and “1” Character range: Matches any single character in the range from first to last.



Match a single character present in either in the range between “a” and “z” or between “A” and “Z”



Match a single character that is not present in either in the range between “a” and “z” or between “A” and “Z”



 Match a single character present in the list “Chars”


Match a single unicode character  eithee any kind of letter from any language (L) or Punctuation (P)

Assertions (Anchors)


Assert position at the beginning of the string


Assert position at the very end of the string


Assert position at the end of the string (or before the line break at the end of the string, if any)



Assert position at the beginning of a line (at beginning of the string or after a line break character) -this meaning outside a character class only



Assert position at the end of a line (at the end of the string or before a line break character)



Assert position at a word boundary


<DECLARE> finds the word DECLARE

Assert Start of word


Assert end of word



Assert position not at a word \b boundary


Assert position at the end of the previous match (the start of the string for the first match)

Grouping Constructs



A subexpression that matches pattern and captures the match. The captured match can be retrieved from the resulting Matches collection using the substitution . To match parentheses characters ( ), use '\(' or '\)'.


 Captures the matched subexpression into a named group. The captured match can be retrieved from the resulting Matches collection using the name. 



Defines a noncapturing group: A subexpression that matches pattern but doesn't capture the match for later use. This is useful for combining parts of a pattern with the "or" character (|). For example, 'entit(?:y|ies) is a more economical expression than 'entity|entities'.


Zero-width positive lookahead assertion: This is a ‘non-capturing match’. The search for the next match begins immediately after the current one, not including the characters that comprised the lookahead.



The Find/Replace  version prevents a match when pattern appears at this point in the expression. This is not quite the same to the zero-width negative lookahead assertion which is also a ‘non-capturing match’. The search for the next match begins immediately after the current one, not including the characters that comprised the lookahead.

(?<= subexpression)

Zero-width positive lookbehind assertion.

(?<! subexpression)


The Find/Replace  version prevents a match when X appears at this point in the expression this is close to the zero-width negative lookbehind assertion.

(?> subexpression)

Nonbacktracking (or "greedy") subexpression.

BackReference constructs



{<(:a+)>}[^:a]+\1>  (finds repeated words)

Backreference. Matches the value of a numbered subexpression.

\{ name }

 Named back-reference. Matches the value of a named expression.


$ number

\ number

\1 \2 (displays the first two captured groups (Tags in MS parlance)

Substitutes the substring matched by capturing group specified by the group number.

${ name }

Substitutes the substring matched by the named group.




Substitutes a literal "$".



Substitutes a copy of the entire match. (not the lookarounds, of course)


Substitutes the whole text of the input string before the match.


Substitutes the entire text of the input string after the match.


Substitutes the last group that was captured.


Substitutes the entire input string.


(?# comment)

Inline 'block' comment. The comment ends at the first closing parenthesis.

# [to end of line]

The comment starts at an unescaped # and continues to the end of the line.

Not all the regular expressions are listed here. I've missed out the Unicode character properties and a lot of the non-standard character shortcuts. For a full list of these, you'll need to refer to the help text provided by Microsoft in their How to: Search with Regular Expressions


So there you have it. Next time you find yourself doing some rather repetitive tasks in SSMS, Visual Studio or Expression Web, then I hope you remember this article and find it useful in helping with a boring job of work. The Find/Replace Regular Expressions are a bit eccentric, and short on advanced features, but they are still very useful on occasion. I know for sure that I'd hate to be without the use of regular expressions in SSMS.

Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 35 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Nice Article. Also, found a typo.
Posted by: Anonymous (not signed in)
Posted on: Friday, January 28, 2011 at 6:20 AM
Message: Nice article. I don't know why Microsoft decided to change the standards. I can understand adding advanced expressions, by why come up with a new expression for something that already has a standard?

The typo: You put ">" instead of "^" for start of line in the comparison table (although the example correctly uses "^".

Thanks for the article.

Subject: Re Typo
Posted by: Phil Factor (view profile)
Posted on: Sunday, January 30, 2011 at 10:17 AM
Message: Oops! Fixed. I'm not sure how that happened.

Subject: Trying to find CREATE with no DBO
Posted by: tskelley (view profile)
Posted on: Wednesday, May 4, 2011 at 12:39 PM
Message: Thank you for the coverage of regex when using with the SSMS Find and Replace dialog. Previously, I was using Find in Files and manually checking each output line to find occurrences where the developer used a CREATE without prefixing the object with dbo.

I tried variations based on one of your examples, but could not figure out how to find where the DBO does not exist. (see below).


Any ideas?

Subject: Make all variable declarations SQL Server 2005 compatible.
Posted by: Senkoken (view profile)
Posted on: Saturday, January 18, 2014 at 6:47 PM
Message: Thanks for the article. I'm trying to find and replace all variable declarations that initializes its vale. For example, I want to replace:

Declare @var int =0;


Declare @var int; Set @var=0;

So far i got this:


Need a little help with data types such varchar(n,i)

Any help apreciated.


Subject: Re: Make all variable declarations SQL Server 2005 compatible
Posted by: Phil Factor (view profile)
Posted on: Tuesday, February 4, 2014 at 2:55 AM
Message: I wouldn't advise using SSMS Regex for developing anything particularly complex, just because it is so quirky. It is much better for the fairly simple stuff.
For anything complex, I use RegexBuddy as my RegEx IDE and do the grunt work in EditPad Pro. It is far, far, faster. It even comments your regex string for you. It is good for developing RegEx strings in a variety of dialects which can then bes put into your .NET or Linux code, but sadly SSMS just isn't one of their dialects!

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Routine SQL DML Testing for the Unenthusiastic Tester

There are more exciting things in life than unit testing SQL Statements, checking the results, timings, and... Read more...

 View the blog

Top Rated

SQL Server Security Audit Basics
 SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and... Read more...

The SQL Server 2016 Query Store: Analyzing Query Store Performance
 There are some obvious advantages to having the Query Store, but what is the performance impact that it... Read more...

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters
 Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is... Read more...

Issue Tracking for Databases
 Any database development project will be hard to manage without a system for reporting bugs in the... Read more...

Releasing Databases in VSTS with Redgate SQL CI and Octopus Deploy
 You can still do Database Lifecycle Management (DLM) workflows in the hosted version of Team foundation... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.