6

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.

jumxozizi
  • 642
  • 10
  • 21

1 Answers1

6

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 ($).
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • `(?m)[\p{Zs}\t]+$` also only matches the spaces on last lines. SSMS 2016 regex syntax may be somehow limited. – jumxozizi Aug 11 '16 at 14:30
  • Try a replacement with a capturing group that should work even in JavaScript. `[^\S\r\n]+(\r?\n?)` -> `$1` – Wiktor Stribiżew Aug 11 '16 at 14:33
  • The alternative removes all spaces and tabs. Leading whitespaces should be left as they are. – jumxozizi Aug 11 '16 at 14:39
  • 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. – Wiktor Stribiżew Aug 11 '16 at 14:40
  • 1
    Find: `[^\S\r\n]+(\r?\n|$)`, replace by `$1` works like a charm, thanks! – jumxozizi Aug 11 '16 at 14:42
  • 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. – Preza8 Jun 19 '18 at 12:29
  • 1
    @Preza8 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. – Wiktor Stribiżew Jun 19 '18 at 12:30
  • 1
    @Preza8 BTW, the question is not about newlines, but about horizontal whitespace. – Wiktor Stribiżew Jun 19 '18 at 12:32
  • @WiktorStribiżew you are right in both comments, I apologize. Thanks for telling me. Unfortunately my vote is locked. – Preza8 Jun 26 '18 at 08:54