We help IT Professionals succeed at work.

Regex test for quotation

I am looking for a regex pattern to  return true for these two string
("SELECT * FROM CIM_DATAFILE WHERE NAME = '" & Replace(sValue, "\", "\\") & "'")
strC = "'" Then


and false for ' "wwwww_0" or anything else

in vba
Comment
Watch Question

Terry WoodsIT Guru
Most Valuable Expert 2011

Commented:
Do you mean these 2 strings?
 ("SELECT * FROM CIM_DATAFILE WHERE NAME = '" & Replace(sValue, "\", "\\") & "'")

Open in new window

and
 strC = "'" Then

Open in new window

?

Author

Commented:
yes
Top Expert 2014

Commented:
Will the Replace() function already have been invoked before you do this comparison?
IT Guru
Most Valuable Expert 2011
Commented:
A regex pattern can look for a value of exactly A or B using the | character, along with ^ and $ placeholders, such that your pattern would be:
^(A|B)$

Open in new window

Using that technique, it's just a matter of escaping the characters in A and B so that they will be interpreted correctly with regards to string parsing for VBA and also for the parsing of the regular expression pattern.

I'm not familiar enough with VBA to be sure about strings, but I suspect " may need to be changed to "" to avoid terminating the string.

There's a regex escape method available here that can be used to help you create your pattern:
https://stackoverflow.com/questions/50440808/excel-vba-regex-escape-method

The code being:
Dim regExEscape As New RegExp
With regExEscape
    .pattern = "[-/\\^$*+?.()|[\]{}]"
    .Global = True
    .MultiLine = False
End With

Open in new window


Used like this I believe:
.pattern = regExEscape.Replace(user_string, "\$&")

Open in new window


Does that help at all?
Terry WoodsIT Guru
Most Valuable Expert 2011

Commented:
ie You'd build up your pattern with code something like this:
pattern = "^("&
regExEscape.Replace("(""SELECT * FROM CIM_DATAFILE WHERE NAME = '"" & Replace(sValue, ""\"", ""\\"") & ""'"")", "\$&")&
"|"&
regExEscape.Replace("strC = ""'"" Then", "\$&")&
")$"

Open in new window