10

I'm trying to perform a find and replace operation in SQL Server 2008 R2 Management Studio and employ a group capture so that I can back reference the groups in the replacement. I understand from this that SSMS uses the Visual Studio 2005 regex engine. Here is an example of what I have:

SELECT First FROM Table1
SELECT Second FROM Table2
SELECT Third FROM Table3

Here is my "Find" expression:

SELECT (.+) FROM (.+)

Here is my "Replace" expression:

\1 \2

However, running the replace operation results in no captured groups being returned, i.e. each line is just " " (<---just the space between the two back-references).

What am I doing wrong?

Community
  • 1
  • 1
rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • I may be off here, but shouldn't you replace with $1, $2, as `\1` is backreferencing to the captured group within the regular expression, but the replace expression, for all intents and purposes, is a new one? – Andris Leduskrasts Jul 08 '15 at 15:17
  • 1
    No $1 and $2 don't work. They return themselves as literals, i.e. "$1 $2". – rory.ap Jul 08 '15 at 15:19

2 Answers2

11

For SSMS 17 and later { ... } and \1 wont work. Use ( ... ) and $1 instead.

Nathan M.
  • 286
  • 1
  • 9
vadzim dvorak
  • 939
  • 6
  • 24
9

SQL Server Management Studio engine uses { and } to "tag" expressions:

SELECT {.+} FROM {.+}

When you combine this with the \1 \2 replacement expression, you get the expected result:

First Table1
Second Table2
Third Table3

VS Regex reference page explains the details.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • So what flavor of regex is that? Is there a reference to that? – rory.ap Jul 08 '15 at 15:29
  • @roryap I don't know if there is a name for this specific flavor of regex, but the doc page I linked talks specifically about this difference: "There are many syntax differences between the regular expressions that can be used in Find what and Replace with and those that are valid in .NET Framework programming. For example, in the Find and Replace window, braces {} are used for tagging expressions to be replaced". – Sergey Kalinichenko Jul 08 '15 at 15:37
  • Any idea how to do the same in SSMS 2016 (13.0.14000.36)? – jumxozizi May 02 '16 at 13:50
  • 2
    For some weird reason, in SSMS they use $1 instead of \1... Not sure what Regex dialect that comes from... – Wouter Oct 07 '16 at 09:29
  • I just tried this in SSMS 2014 and `$1` is just treated literally whilst `\1` returns nothing; `\0` seems to return the entire expression however. Just for the reference, the REGEX I used was: `R(M|D|T)`, matches were found and `M`, `D` or `T` should have been returned for `\1`. – Matt Arnold May 28 '19 at 11:42
  • 1
    @Wouter - Fair bit of prior art does that, PHP, JavaScript, C#... `\1` and such are used *within* an expression to refer to a capture (e.g., `(["']).*?\1` to match something between `"` and `"` or `'` and `'` but not `"` and `'`), but in a *replacement pattern*, it's typically `$1` and such. – T.J. Crowder Jun 26 '19 at 16:13