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.

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