Solved

Regex replace in SSMS

Posted on 2013-06-10
2
336 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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