Remove Square Brackets in T-SQL Using Regex

identifiersql serverssmst-sql

I'm using SQL Server Management Studio 17.4, which supports search-and-replace using a Microsoft version of regular expressions (regex).

I need a regex expression that can reliably remove square brackets from source code, such as large CREATE TABLE statements that have been scripted from existing an database.

I'm currently using this regex in the "find" dialog:

\[((?!\d+)(?!PRIMARY))(([A-Z]|_|[0-9])*?)\]

The "replacement" is $2.

It seems to work very well, however I'm concerned this may eliminate required square brackets under some conditions.

In the regex above, the (?!PRIMARY) piece ensures ON [PRIMARY] does not have the square brackets removed. Are there other exceptions I need to be aware of?

I'm willing to exclude the possibility of column names being reserved words, such as [GROUP]. The environment I work in has specific naming convention that typically eliminates that possibility.

Best Answer

Essentially what you've done with PRIMARY is started building a list of exceptions, like people do when they start trying to thwart SQL injection the hard way (by "cleansing" the SQL). Do you want to do this for all current and future reserved words? Even in a strict environment things will slip through, especially if things aren't reserved words yet but become reserved words later (think about people who named their string splitting function dbo.STRING_SPLIT() before SQL Server 2016).

Here are just a handful I thought of off the cuff; there are dozens and dozens more. Your RegEx probably captures some of these (like dashes and spaces) but, if you remove any of the square brackets that remain after your RegEx has run, this script will break:

CREATE TABLE dbo.[Create]
(
  [Alter]      int,
  [Drop]       int,
  [Begin]      int,
  [End]        int,
  [Grant]      int,
  [Deny]       int,
  [Revoke]     int,
  [Truncate]   int,
  [Identity]   int,
  [Select]     int,
  [Update]     int,
  [Insert]     int,
  [Delete]     int,
  [Merge]      int,
  [Procedure]  int,
  [Trigger]    int,
  [Table]      int,
  [Function]   int,
  [View]       int,
  [Raiserror]  int,
  [bad-name]   int,
  [worse name] int,
  [0xd83d]     int,
  [?]        int
);
GO

Get over your hatred for the square brackets and get used to them - they will someday protect you from someone who named something incorrectly.

Related Question