CODEWITHSUNDEEP
vbscript
Vishy
Vishy

Reputation: 295

String between a line

By some google resources I could write a program to extract the string between two specific strings. I could not print value or store the value into a variable using regular expressions. Here is my code.

prgName = InStr(vText, "Program")
sub_prgName = Mid(vText, prgName, 100)
MsgBox sub_prgName, vbInformation
Dim RegEx: Set RegEx = New RegExp
RegEx.IgnoreCase = True
RegEx.Pattern = "Program(.*)?Variant"
Set RegEx = RegEx.Execute(prgName)
MsgBox RegEx.Value, vbInformation

I want to get the string b/w Program and Variant. When try to see the output it says

Run time error 438, object doesn't support this property.

This is the value that I want to parse using RegEx:

Program

sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql

Variant

Upvotes: 1

Views: 91

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200213

When in doubt, read the documentation. The Execute method returns a Matches collection, so you need to iterate over that collection to get the desired result (in your case the first submatch).

For Each m In RegEx.Execute(prgName)
  MsgBox m.SubMatches(0), vbInformation
Next

Demonstration:

>>> s = "Program sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql Variant N/A"
>>> Set re = New RegExp
>>> re.Pattern = "Program(.*)?Variant"
>>> re.IgnoreCase = True
>>> For Each m In re.Execute(s) : WScript.Echo m.SubMatches(0) : Next
 sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql

Theoretically you could also do this without the loop:

Set m = RegEx.Execute(prgName)(0)
MsgBox m.SubMatches(0), vbInformation

However, RegEx.Execute(prgName)(0) would raise an error if the regular expression didn't find a match, so evaluating the results in a loop is the safer approach.

I'd remove the ? in your regular expression, though, because it'll make the group optional, so you're not guaranteed to have a SubMatches(0) item. Simply use Program(.*)Variant instead. If there's no text between "Program" and "Variant" you'll get a zero-length string as the first submatch. Or you could put it inside the parentheses right after the asterisk (Program(.*?)Variant) to make the match non-greedy (shortest match instead of longest match).

If your input string contains newlines you need to use [\s\S] instead of ., because the dot in regular expressions matches any character except newlines.

Demonstration:

>>> s = "Program" & vbNewLine & vbNewLine _
& "sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql" _
& vbNewLine & vbNewLine & "Variant"
>>> WScript.Echo s
Program

sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql

Variant
>>> Set re = New RegExp
>>> re.Pattern = "Program(.*)?Variant"
>>> re.IgnoreCase = True
>>> For Each m In re.Execute(s) : WScript.Echo m.SubMatches(0) : Next
>>> re.Pattern = "Program([\s\S]*)?Variant"
>>> For Each m In re.Execute(s) : WScript.Echo m.SubMatches(0) : Next


sqlplus $SOPS_MIPO_USER/$SOPS_MIPO_PASSWORD@$DB_SID @mipo_pruning.sql

As a side note, you shouldn't replace your regular expression object with the result of the Execute method.

Set RegEx = RegEx.Execute(prgName)    '<-- NEVER do this!

Re-using variables is a no-no, so don't do it.

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

.Execute returns a collection of Match objects. This collection has a .Count but no .Value property. You can use MsgBox RegEx(0).Value to get the .Value of the first match.

Evidence:

>> sInp = "XXXProgramYYYVariantZZZ"
>> Set r = New RegExp
>> r.Pattern = "Program(.*?)Variant"
>> WScript.Echo r.Execute(sInp)(0).Value
>> WScript.Echo r.Execute(sInp)(0).SubMatches(0)
>>
ProgramYYYVariant
YYY

You should publish your input(s) and expected result(s).

Upvotes: 0

Related Questions