We help IT Professionals succeed at work.

RegEx Needed for VBA Code

Mariyam
Mariyam asked
on
Medium Priority
526 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
Comment
Watch Question

Top Expert 2005

Commented:
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]
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Top Expert 2005

Commented:
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]+\]
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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?
Top Expert 2005

Commented:
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?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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?
Top Expert 2005

Commented:
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.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
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

Author

Commented:
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.
Top Expert 2005

Commented:
>>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?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
WMIF,
Yes, it uses exactly the same strings for Find and for Replace in VBA as it does through the user interface.
Graham
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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]
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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
 
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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,}\]

Author

Commented:
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
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.