Regex Pattern Using VBA

Question

I'm currently using VBA and regex test an update i need to do with one of my fields.

My current SQL update is like this:

UPDATE Product SET Product.[Short description test] = MyRegexReplace(Product.[Short description], "[\(]+[A-z0-9\/]+[\)]", "");

As you can see the regex I'm using is [\(]+[A-z0-9\/]+[\)] and replacing it with nothing.

The VBA script i am using is as below (I am using the reference Microsoft VBScript Regular Expressions 5.5):

Option Compare Database
Option Explicit

Public Function MyRegexReplace( _
        originalText As Variant, _
        regexPattern As String, _
        replaceText As String) As Variant
    ' VBA Project Reference required:
    '  Microsoft VBScript Regular Expressions 5.5
    Dim rtn As Variant
    Dim objRegExp As RegExp, objMatch As Match, colMatches As MatchCollection

    rtn = originalText
    If Not IsNull(rtn) Then
        Set objRegExp = New RegExp
        objRegExp.pattern = regexPattern
        Set colMatches = objRegExp.Execute(originalText)
        For Each objMatch In colMatches
            rtn = _
                    Left(rtn, objMatch.FirstIndex) & _
                    replaceText & _
                    Mid(rtn, objMatch.FirstIndex + objMatch.Length + 1)
        Next
        Set objMatch = Nothing
        Set colMatches = Nothing
        Set objRegExp = Nothing
    End If
    MyRegexReplace = rtn
End Function

My Problem is that most of the strings i am replacing it works fine on. As the aim is to remove anything in and including the curly brackets.

The issue is that on some longer string such as:

This is a test string [10876DB] =589+608+612= (P17/S)

it changes the string to:

This is a test string [10876DB] =589+608

Now i'm pretty sure that this is because my regular expression is matching things that it should not be matching and replacing them with nothing.

I have also tried this (which i have taken and edited from this post Regex: delete contents of square brackets):

\((?:[^\(\)]++|(?0))*+)

However, this just brings up an error. Can anyone advise on anything that may help me with this situation?

Thank you in advance.

The thing with `\((?:[^\(\)]++|(?0))*+)` is that it contains 2 things that vba's regex doesn't support: 1. Possessive quantifiers (`+` after another quantifier) and 2. Recursive regex (the `(?0)` part). You can however simply use MElliott's regex, and perhaps add a `\s*` to it: `\s*\([^\)]+\)`. On the other hand, I don't find anything wrong with the regex you've been using =/ — Jerry, Jan 14 '14 at 17:52
@Jerry thats because although i over complicated the regex compaired to MElliott's. I am also an idiot as i created a test field and forgot to reset the string length to 255 so it defaulted to 50. Thank you however for your input — Liam Sorsby, Jan 14 '14 at 17:56

score 0 · Accepted Answer · answered Jan 14 '14 at 17:48

0

How about just this:

\([^\)]+\)

Example: http://regex101.com/r/eH4uW8

answered Jan 14 '14 at 17:48

Bryan Elliott

4,055
2
21
22

this worked fine but it was also an error of mine as i created a new field in access and forgot to reset the string length from 50 to 255 but i will mark this as correct when it allows me to – Liam Sorsby Jan 14 '14 at 17:53

score 0 · Answer 2 · answered Jan 14 '14 at 17:50

0

As the aim is to remove anything in and including the curly brackets. You mean square brackets or parenthesis?

Just use [\[(].*?[\])]? Then I would probably do a replace on {space}{space}+ with {space} to fix the excessive spacing created.

answered Jan 14 '14 at 17:50

tenub

3,386
1
16
25

I mean neither i mean ( and ) – Liam Sorsby Jan 14 '14 at 17:53

Regex Pattern Using VBA

2 Answers2