RegEx Replace in SSMS

· by · in

Refactoring Transact-SQL code can be a time-consuming and frustrating affair for the trusty code-reviewing Database Developer. This post shows several techniques I use to refactor code via the little-understood RegEx Replace functionality found in SQL Server Management Studio, or SSMS as its colloquially known.

Pressing CTRL-H displays the “Find and Replace” tool in the SSMS Query Window:

Find and Replace tool window in SSMS

The trusty Find-and-Replace tool window, with the RegEx option enabled.

The above window shows the option for “Use Regular Expressions (CTRL-E)” is selected (its the square-and-star symbol below “Replace”, highlighted in a dull yellow). Ensure that option is selected prior to using the examples shown in this blog post.

  1. line endings. Occasionally, someone will be using a Linux editor to update Transact-SQL code, and will save it with line endings that are a mix of CR\LF and LF characters. Converting the newline line-endings to carriage-return\line-feed line-endings can be accomplished using (?<!\r)\n in the “Find” text box, and \r\n in the “Replace” text box.

  2. Convert tabs to spaces. Yes, this is a holy war, however, as a developer you can make more money using spaces instead of tabs. Go change the option in SSMS per the image below, then using \t in the “Find” box, and (that’s 4 spaces) in the “Replace” box. Thank me later when your boss gives you that surprise 10% raise you’ve been expecting.

Spaces make you more money than tabs, this image shows where to set the option in SSMS.

Configure SSMS to insert spaces when you press the TAB key.

  1. Extra spaces at the end of a line. Removing extra tabs or spaces from the end of a line is easily accomplished using (\t| )+((\r\n)|\n) in the “Find” box, and \r\n in the “Replace” box. Essentially, this looks for tabs or spaces followed by either CR\LF or LF by itself, and replaces that sequence with CR\LF, just the way god1 intended.

  2. Move commas from the end of each line, to the start of the next line. This one is contentious, so do this only if you’re a leading-commas person like myself. Search for ,(\s+)((\r\n)|\n)(\s+) and replace it with \r\n$4, (NOTE, there is a space at the end of the replace text that is not rendered correctly in the blog post).

1 – I’m not a believer, I choose the path of “show me the money”, and “as god intended” is just a saying I used for effect. Don’t take it so seriously.