Applied SQL: Find and replace using regular expressions in SSMS

Regular expressions are searches on steroids, the wildcats of the wildcard world*.  Even if you  never ever write CLR, regex (as it’s affectionately known) can be useful to you today, right now.

1. Open a new query window in SQL Server Management Studio.
2. Type “xyz”, hit enter twice, and type “xyz” again.
3. Now hit CTRL-H to pull up your Find and Replace dialogue.
4. Under Find What, type \n\n. Under Replace With, type \n. 
5. Select Options > Use > Regular Expressions.
6. Hit Replace All. Watch your double spaced script magically turn single spaced.

This is the first of many simple uses for regex in SSMS. With just a little poking around online, you can find every reference to a table in a script, regardless of whether it’s bracketed or not ([stats].Tbl and stats.Tbl and stats.[Tbl], etc.).  Or, replace all instances of varchar(…) with varchar(500) in your stored procedure. Or, well, whatever you need!

Go on, play around with some regex. Check out Technet’s “Search Text with Regular Expressions” and Simple Talk’s “RegEx-Based Finding and Replacing of Text in SSMS” for more.

And…

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

P.S. As long as we’re on useful work habits, you should really Learn to Type and get to know common (and custom) keyboard shortcuts, too.

*Oh, what alliteration!!