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.

Diffraction

Try using [\p{Zs}\t]+$ – Extraterritoriality

[\p{Zs}\t]+$ matches the two spaces on last line, nothing else. – Diffraction

That means you need to turn on multiline mode with (?m). (?m)[\p{Zs}\t]+$ – Extraterritoriality

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

Trotta

(?m)[\p{Zs}\t]+$ also only matches the spaces on last lines. SSMS 2016 regex syntax may be somehow limited. – Diffraction

Try a replacement with a capturing group that should work even in JavaScript. [^\S\r\n]+(\r?\n?) -> $1 – Extraterritoriality

The alternative removes all spaces and tabs. Leading whitespaces should be left as they are. – Diffraction

Sorry, sure, I made a mistake. Fixed. I thought about a different case. (\r?\n|$) will match either at the end of the line (optional CR and obligatory LF) or at the end of the line. – Extraterritoriality

Find: [^\S\r\n]+(\r?\n|$), replace by $1 works like a charm, thanks! – Diffraction

None of the provided answers have worked for me. It just says that it couldnt find it. I can clearly see newlines in the file. – Commutation

@Commutation It does not mean the answer is wrong and is worth downvoting. If you need help, post a separate question, or at least let others help you - but tell exaclty what your input is. Downvoting is OK when the answer is poorly formatted, does not answer the question, code only, spam, but it is not the case. – Extraterritoriality

@Commutation BTW, the question is not about newlines, but about horizontal whitespace. – Extraterritoriality

@WiktorStribiżew you are right in both comments, I apologize. Thanks for telling me. Unfortunately my vote is locked. – Commutation