?
Solved

RegEx Needed for VBA Code

Posted on 2006-11-02
19
Medium Priority
?
515 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:Mariyam
  • 9
  • 5
  • 5
19 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 17863163
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
ID: 17865249
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
ID: 17868585
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17868809
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
ID: 17869103
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
ID: 17869230
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
ID: 17869241
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
ID: 17869466
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:Mariyam
ID: 17870711
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
 

Author Comment

by:Mariyam
ID: 17870774
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
ID: 17872265
>>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
ID: 17874335
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
ID: 17874408
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
ID: 17874430
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
ID: 17880489
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:Mariyam
ID: 17883370
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:Mariyam
ID: 17883448
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 17883969
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:Mariyam
ID: 17892025
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

757 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