[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Regex replace in SSMS

Posted on 2013-06-10
2
Medium Priority
?
353 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 2000 total points
ID: 39236409
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
ID: 39236427
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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