Solved

Regex replace in SSMS

Posted on 2013-06-10
2
329 Views
Last Modified: 2013-06-13
In SSMS I would like to include square brackets for schema. If dbo is schema name is dbo otherwise the schema name is xxxx.yyyy

dbo.spSomeName should be replaced as [dbo].[spSomeName]
dbo.tSomeName   should be replaced  as  [dbo].[tSomeName]
MyDB.AnySchema.spSomeName should be replaced  as  [MyDB.AnySchema].[spSomeName]
MyDB.AnySchema.tSomeName should be replaced  as  [MyDB.AnySchema].[tSomeName]

How to achieve this using Regex replace? Please suggest
0
Comment
Question by:Easwaran Paramasivam
  • 2
2 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
Comment Utility
Regular expressions in SSMS are really horrible and non-standard, even by Microsoft's track record!

Documentation is available here: http://msdn.microsoft.com/en-us/library/ms174214%28v=sql.90%29.aspx

My forte is Regular Expressions in general, and I'm not a Microsoft programmer, so I can't test this for you, however I think this might work for you:

Match:
{([a-zA-Z0-9_]+\.)*[a-zA-Z0-9_]+}\.{[a-zA-Z0-9_]+}
Replace with:
[\1].[\2]

It will also match cases like:
otherstuff.morestuff.MyDB.AnySchema.SomeName

If that's a problem, we can probably alter the pattern to fix that.

Let me know how it goes.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
This might be slightly better (ie I think it fixes the problem I previously pointed out), and uses the same replacement:

Match:
{([a-zA-Z0-9_]+\.|)[a-zA-Z0-9_]+}\.{[a-zA-Z0-9_]+}
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now