Search and Replace in SSMS with Regular Expressions

Technical Value

In this article I want to show two examples on how regular expressions can be used for transforming SQL queries in SSMS. This can be useful, if you want to customize SQL queries to extract a list of columns from a DDL statement or insert structured data via sql.

These examples work only in the SQL Server Management Studio, because it uses a special, stripped off subset of regular expression constructs and different syntax in some parts.

1. Extract the column list from a table definition

In the "Search And Replace" dialog put in the following:

  • Find what:  ^{:b*\[*[^\[\]"]+\]*}:b[^,]+
  • Replace with:  \1,

It changes this:

  1.     [Column1] [bigint] NOT NULL,
  2.     [Column2] [datetime] NULL,
  3.     [Column3] [nvarchar](50) NULL

to this:

  1.     [Column1],
  2.     [Column2],
  3.     [Column3]

If you want to remove the preceding whitespace at the same time, then change the "Find what" string to:

  1. ^:b*{\[*[^\[\]"]+\]*}:b[^,]+

This also works for more loosely written listings, like:

  1. Column1 [bigint] NOT NULL,
  2. [Column2] datetime,
  3. [Column 3] nvarchar(50) NULL

2. Create INSERT INTO script from text data

If you have tab-separated data, copied from a result set in SSMS, you can generate an INSERT INTO query out of it. This example works on rather simple data, without blobs or line breaks. Target is a statement like this:

  1. INSERT INTO [table]([col1],[col2])
  3. (123, 'abc'),
  4. (456, 'cde')

All editing will be performed in different steps. We use regex to generate the values list. The remaining code has to be added manually. If the data contains a column list, copy and remove it, so it will not be changed. You can add it later, when the replacements are done.

Masking single quotation marks:

If there are embedded single quotation marks, then you have to mask them, before adding new ones to the string values.

  • Find what: (')
  • Replace with: ''

Adding single quotation marks at the beginning and end of the line:

  • Find what: (^)|($)
  • Replace with: '

Adding single quotation marks around delimiters:

  • Find what: (\t)
  • Replace with: ', '

Removing single quotation marks around numbers:

  • Find what: '~(\.){[\+\-]@<([0-9]@\.[0-9]*E[\+\-]*[0-9]*)|([0-9]*\.[0-9]*)|([0-9]+)}'
  • Replace with: \1

This pattern matches any kind of number, like negative, positve and with or without decimal separator. It ignores numbers without a leading digit.

Removing single quotation marks around NULLs:

  • Find what: '{NULL}'
  • Replace with: \1

Adding a parenthesis at the beginning of the line:

  • Find what: ^
  • Replace with: (

Adding a parenthesis with comma at the end of the line:

  • Find what: \n
  • Replace with: ),\n

The closing parenthesis on the last line has to be added manually.   Finish off by deleting surplus characters (if any) and adding the necessary code at the beginning to complete the query ("INSERT INTO..., VALUES"). Thanks to Phil Factor for providing a solution to remove quotation marks around numbers.


Mi, 17.05.2017 - 18:50

In SQL Server 2016 Management Studio, when I use Regular Expressions to put a quote after each item in a vertical list (replace $ with '), it inserts a new line then the quote:
ghi becomes
How can I avoid the inserted blank line?

Fr, 19.05.2017 - 12:03

There are syntax differences when using regular expressions in SSMS or Visual Studio. For the most part the syntax of Microsoft .NET Framework regexes are supported, but not in all cases.

The following refers to Visual Studio 2013 and newer. Currently I have no access to SSMS 2016, but Visual Studio 2013 shows the same behaviour.

The end of a line in VS is defined as \r?$. But this expression is not very useful for replacements. One working expression for searching and replacing at the end of a line is: \r?\n

So, to place a comma at the end of a line, you can use:

Find: \r?\n
Replace: ,\n

But this omits the lasst line, if there is no subsequent line break.

For a complete replacement, including the last line, the regexes have to look like this:

Find: ((\r?\n)|$)
Replace: ,$0


Match a linebreak (\r?\n) or "end of line" ($).

The $0 in the replacement refers to the first capturing group in the find expression, which is (\r?\n). So the linebreak will be reused in the replacement, if there is one.

Hope this helps.

Btw: Microsoft has a good documentation about regex in VS:


Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden