RegEx Replace in SSMS
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.
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.
- 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)\nin the “Find” text box, and
\r\nin the “Replace” text box.
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
\tin 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.
- 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\nin 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.
Move commas from the end of each line, to the start of the next line. This one is contentions, 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.