replace a column using regex in ms access 2010

Question

There's a table named sample including only one column, body (type: text) in ms access 2010:

<name>John</name><age>12</age>

I'd like to delete every string inside the brackets. See this:

John12

I added Microsoft VBScript Regular Expression 5.5 library and create this module:

Function Replace_Regex(str1, patrn, replStr)
Dim regEx
Set regEx = New RegExp
regEx.Pattern = patrn
regEx.IgnoreCase = True

Replace_Regex = regEx.Replace(str1, replStr)
End Function

And then, I run this query: update sample set body = Replace_Regex(body, "<[^>]+?", "")

But the result is:

 ame>John</name><age>12</age>

So, what's the problem?

Answer 1

Add this to the function:

regEx.Global = True

Then use "<[^>]*>" as the pattern.

Here's what I get in the Immediate window:

body = "<name>John</name><age>12</age>"
? Replace_Regex(body, "<[^>]*>", "")
John12