Solved

RegEx Needed for VBA Code

Posted on 2006-11-02
19
493 Views
Last Modified: 2008-01-16
Hi,

Would someone please provide me with a regular expression I can use in VBA code with the Application/Range.Find object that will locate a text string in the following format:

                [server].[dataBase].[owner].[Table]

TIA
-Mari
0
Comment
Question by:FoxProMari
  • 9
  • 5
  • 5
19 Comments
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
what different combinations are you expecting?  are you looking for any text with those brackets around them?

"\[\w\]\.\[\w\]\.\[\w\]\.\[\w\]"

that pattern will find these results:

[server].[dataBase].[owner].[Table]
[asdf].[asdf].[asdf].[asdf]
[qwer].[qwer].[qwer].[qwer]
[qwer].[dh].[ghjkgh].[rtkklg]

it will not find these:
[server].[dataBase].[owner]
[server123].[dataBase].[owner].[Table]
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
This will restrict the elements within the square brackets to be only letters or digits:
\[[a-zA-Z0-9]{1,}\].\[[a-zA-Z0-9]{1,}\].\[[a-zA-Z0-9]{1,}\].\[[a-zA-Z0-9]{1,}\]

Don't forget to set MatchWildcards to True.
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
ah GrahamSkan, you got in there the count which i forgot, but you missed the escape for the period.  we can actually shorten the pattern down.

(\[[\w\d]+\]\.){3}\[[\w\d]+\]

this will let you specify how many groups you want to see.  we could also use this pattern to have an varried amount of groups.

(\[[\w\d]+\]\.)*\[[\w\d]+\]
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Hi WMIF,
While we are waiting for the questioner, I will take the opportunity to learn something.

I can't get either of your suggestions to work. Also I don't really understand them. What is the reason for escaping the dot, for instance?
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
in regex, the period is a wildcard for any character but a new line.  its basically the same as using a pattern something like this:
[A-Za-z0-9!@#$%^&*()<>?:"{}[]\|';,./=+-_]

(there are a few other characters that need escaping in the above pattern, but i didnt want to confuse the idea)

so without escaping the periods, you would match all of the below:
[server].[dataBase].[owner].[Table]
[server]2[dataBase]5[owner]g[Table]
[server]+[dataBase]-[owner].[Table]
[server]s[dataBase]r[owner]w[Table]
[server]9[dataBase]&[owner]~[Table]


also, the \w and \d are keywords that represent sets of characters.  \w is any word character and \d is any digit.  i just double checked my pattern with a tester i built and it works fine.  how are you testing it?
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
I'm testing it in Word. I'm not sure that your strings are relevant to the Word Find using Wildcards. The @ represents any character. Have your tried your suggestions in the Word Find environment?
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
hmm, nope i havent tried that.  i saw that the asker was looking for vba code.  is that what the range.find does?  sorry, i dont work much with word.
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Word has its own version of regular expressions in the .Find object, called 'wildcarding' which pre-dates the generic Microsoft RegEx. I expect that Microsoft will eventually introduce a way of using RegEx without breaking existing applications, but I don't think that they're there yet.
0
 

Author Comment

by:FoxProMari
Comment Utility
Sorry for the delay, but thanks so much for working on this in the meantime.

Actually the delay was helpful because I'm using this code to look through some SQL Stored Procedures which have been migrated from previous SQL versions to Yukon 2005.  The procedures require some minor rewriting due to the upgrade, but I need to identify them first.  SQL 2005 no longer allows the format of [server].[dataBase]..[table] so I guess the only two formats which we need to flag are:

[server Name].[dataBaseName].[schema/owner].[table Name]
[dataBaseName].[schema/owner].[table Name]
[dataBaseName]..[table Name]

The first character must be one of the following:
1.  Letters A-z
2.  The underscore (_), at sign (@), or number sign (#).  

Subsequent characters:
1.  Letters A-z
2.  The numbers 0-9
3.  The at sign (@), dollar sign ($), number sign (#), or underscore (_)

I hope this is a sufficient amount of information.  Thanks again for all of your assistance and have a great weekend.

-Mari
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:FoxProMari
Comment Utility
Oh and just to clarify, I created a macro in Word 2003 to create the core code and then went into the VBA editor to modify it.  Sorry about the confusion.  It good to know though that there is a reason why the code submitted is not working.
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
>>Oh and just to clarify, I created a macro in Word 2003 to create the core code and then went into the VBA editor to modify it.

so does the vba in word use this special word version of regex also?
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
WMIF,
Yes, it uses exactly the same strings for Find and for Replace in VBA as it does through the user interface.
Graham
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Also WMIF,

The {3} to repeat the finds works, but only in the sense that it if it finds one example, it then looks for exactly the same string. It does not re-apply the rules.

Thus out of your examples, it would only find the second and third

[server].[dataBase].[owner].[Table]
[asdf].[asdf].[asdf].[asdf]
[qwer].[qwer].[qwer].[qwer]
[qwer].[dh].[ghjkgh].[rtkklg]
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
FoxProMari,
I'm getting reports from the Find process saying that the expression is too complex. I'm trying to simplify it, but I think that we may have to put more into the VBA code, and less into the Find mechanism.

If your handle is anything to go by, it shouldn't be too difficult to sort out the finer details. Just put a 'then'  in at the end of an If statement etc

Contact you later, Graham
 
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
I hope this is rigorous enough. To keep it relatively simple, I've had to allow the dollar sign as an acceptable first character. I take it that the "/" in the first two examples two examples is literally that.

I suggest that you have three searches, one for each pattern, though the second search will find matches embedded in the examples of the first pattern.  

[server Name].[dataBaseName].[schema/owner].[table Name]
\[[_@#$a-zA-Z]{1,}\].\[[_@#$a-zA-Z]{1,}\].\[[_@#$a-zA-Z]{1,}/[_@#$a-zA-Z]{1,}\].\[[_@#$a-zA-Z]{1,}\]

[dataBaseName].[schema/owner].[table Name]
\[[_@#$a-zA-Z]{1,}\].\[[_@#$a-zA-Z]{1,}/[_@#$a-zA-Z]{1,}\].\[[_@#$a-zA-Z]{1,}\]

[dataBaseName]..[tableName]
\[[_@#$a-zA-Z]{1,}\]..\[[_@#$a-zA-Z]{1,}\]
0
 

Author Comment

by:FoxProMari
Comment Utility
Geez, I complete forgot to advise you that to simplify matters I strip out the square braces, sorry.

I tried to use a couple of your examples and it's still not finding anything.  The closest I've gotten is the following format but even which will locate "requiredtext.requiredtext" but when I attempt to extend it, it stops finding anything and eventually states that the FIND is too complex just like the message you're receiving.

What do you think of the following approach.  I might have to resort to listing all of our server and database names and just searching the documents for any references to them if I can't get this to function in the near future.

Thanks for all of your help.

-Mari
0
 

Author Comment

by:FoxProMari
Comment Utility
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
I'm not clear about when you strip out the square brackets. Do you do that before you run the Find? If that is the case, these three respectively find each string written above.

serverName.dataBaseName.schema/owner.tableName
[_@#$a-zA-Z]{1,}.[_@#$a-zA-Z]{1,}.[_@#$a-zA-Z]{1,}/[_@#$a-zA-Z]{1,}.[_@#$a-zA-Z]{1,}

dataBaseName.schema/owner.tableName
[_@#$a-zA-Z]{1,}.[_@#$a-zA-Z]{1,}/[_@#$a-zA-Z]{1,}.[_@#$a-zA-Z]{1,}

dataBaseName..tableName
[_@#$a-zA-Z]{1,}..[_@#$a-zA-Z]{1,}

I don't understand the relevance of the link.

0
 

Author Comment

by:FoxProMari
Comment Utility
Thanks for all of your help, I REALLY appreciate it.  

Because I have access to the server I was able to reduce the information for the Server Name to just 0-9 & A-z.  If I hadn't been able to verify that no special characters were being used, the expression necessary to cover all possibilities would have become too complex so I fudged it a bit.  But I learned a lot from this exchange.

                Selection.Find.ClearFormatting
                With Selection.Find
                    .Text = _
                        "<[A-z0-9]{1,}>[.]<[0-9A-z_]{1,}>[.]<[0-9A-z_]{1,}>[.]<[0-9A-z_]{1,}>"
                    .Replacement.Text = ""
                    .Forward = True
                    .Wrap = wdFindContinue
                    .Format = False
                    .MatchCase = False
                    .MatchWholeWord = False
                    .MatchAllWordForms = False
                    .MatchSoundsLike = False
                    .MatchWildcards = True
                End With

                Selection.Find.Execute
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

771 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

11 Experts available now in Live!

Get 1:1 Help Now