THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Another RegEx Trick for SSMS

So I wanted to have some consistency in some T-SQL code for bracketed identifier use. Objects in the code had bracketed identifiers in some cases, and not in others (sometimes even on the next line of code) and so I saw both TableName and [TableName]. Again, RegEx saved the day. For any given object OBJECT_NAME, use the following in the Search and Replace dialog (using the RegEx option):

  1. Find what:
    {[^\[]}{<OBJECT_NAME>}{[^\]]}
  2. Replace with:
    \1\[\2\]\3

This will replace the OBJECT_NAME with [OBJECT_NAME] without losing surrounding white space or punctuation and without doubling up the brackets. So if I had 
  [SomeSchema].OBJECT_NAME
and did this replace, it would then be
  [SomeSchema].[OBJECT_NAME]

And of course [SomeSchema].[OBJECT_NAME] would not be affected, so you would not end up with [SomeSchema].[[OBJECT_NAME]] after replacing.

But of course, be wise and either replace one at a time, or if you choose to replace all, always double check your code afterwards.

Published Tuesday, September 19, 2006 11:53 PM by Peter W. DeBetta

Comments

No Comments
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement