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.
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 ($).