Home » Applied SQL »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

Applied SQL: Find and replace using regular expressions in SSMS

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


Happy days,
Jen McCown

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!!

Currently there are "2 comments" on this Article:

  1. […] *Among other Cool Things: the greatness of Powershell, XCKD, New Skin Liquid Bandages, standing desks, using underscores with LIKE in T-SQL, the Pandemic board game, Adam Savage’s Tested.com podcast, and using RegEx in SSMS. […]

  2. […] past couple of years, I never really picked up on them in SSMS. I spotted Jen McCown’s recent blog post on the same subject and I thought, rights that it, I'm going to spend the time to learn […]

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 20 hours 23 minutes 59 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/