Using regex replace in SSMS 2016 to trim lines

Question

How can I use SSMS 2016 regex replace feature to remove extra spaces and tabs at the end of lines?

Example of editor content:

select
    'tab'   
,   'space' 

select
    'tabs'      
,   'spaces'  

Goal:

select
    'tab'
,   'space'

select
    'tabs'
,   'spaces'

In SSMS 2012 find string :b*$ matches those extra tabs and spaces and allows me to replace them with nothing. SSMS 2016 started using some kind of .net regex syntax for the find/replace feature. Using \s+$ almost works in 2016, but it removes the empty lines.

Answer 1

To remove trailing horizontal whitespace from the end of the lines with a .NET regex, you need to use

(?m)[\p{Zs}\t]+$

The multiline modifier (?m) is necessary to make the $ anchor match end of lines rather than the whole strings. The \p{Zs} matches any Unicode horizontal whitespace but a tab char, thus, we need to add \t and \p{Zs} into a character class [...]. The + quantifier will match 1 or more occurrences of these whitespaces.

An alternative that does not rely on a multiline modifier:

[^\S\r\n]+(\r?\n|$)

and replace with $1 backreference (that re-inserts the text captured by the first (and only) capturing group in the pattern, i.e. to preserve end-of-lines in the output).

Details:

• [^\S\r\n]+ - matches 1 or more characters other than non-whitespaces, CRs and LFs (basically, an \s with the exception of \r and \n)
• (\r?\n|$) - matches either at the end of the line (optional CR, carriage return, and obligatory LF, newline) or at the end of the string ($).