Link to home
Start Free TrialLog in
Avatar of Mariyam
Mariyam

asked on

RegEx Needed for VBA Code

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
Avatar of WMIF
WMIF

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]
Avatar of GrahamSkan
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.
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]+\]
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?
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?
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?
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.
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.
Avatar of Mariyam

ASKER

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
Avatar of Mariyam

ASKER

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.
>>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?
WMIF,
Yes, it uses exactly the same strings for Find and for Replace in VBA as it does through the user interface.
Graham
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]
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
 
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,}\]
Avatar of Mariyam

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mariyam

ASKER

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