How to replace text within string matching a pattern

**ADezii** · May 27 '10, 10:02 PM

Originally posted by postman

I need to remove all 8 or 9-digit numbers from memo fields in a table. They are at varying positions within the fields.

I was able to construct a SELECT query to retreive all the records that contain these numbers, using "Like" and a pattern, but I can't figure out a query solution to replace these numbers with a mask, like "********** ".

Can probably construct a VBA solution to loop through all the characters of each memo field, but I'd like to find an easier/better solution, if one exists.....

If this is not possible, do you know if there is a way to return only the substrings that match the query criteria? For example, if a memo field has "this is for customer #99999999 calling about an issue", can you just return the "99999999" part in the query instead of the whole field?

Thank you very much in advance for any help you can provide.

A VBA based solution should be easy to construct, but it would be very time consuming.

**postman** · Jun 1 '10, 02:19 PM

I went with the VBA option, and it ran fairly fast through the 10,000 records that matched the criteria--took less than 30 seconds.

I created a SELECT query with the pattern criteria, then ran an UPDATE query on that with the VBA function in the "SET" argument:

UPDATE patternQuery SET patternQuery.No teField = replcNum([patternQuery].[NoteField],"********") ;

There's probably a better way to do this, but this is what I came up with. Basically, it loops through every character in the string and if it's a number, it adds it to a number string variable. Once that number string gets to the specified length (8,9,or 12 in my example), it's replaced with the mask.

Code:

Function replcNum(str As String, maskTxt As String) As String
Dim subStr As String
Dim char As String
Dim i As Integer

For i = 1 To Len(str)
    char = Mid(str, i, 1)
    
    If IsNumeric(char) Then
        'Number string
        subStr = subStr & char
        
        If i = Len(str) And (Len(subStr) = 8 Or Len(subStr) = 9) Or Len(subStr) = 12 Then
            'Replace number with mask
            str = Replace(str, subStr, maskTxt)
        End If
    ElseIf IsNumeric(char) = False Or i = Len(str) Then
        If Len(subStr) = 8 Or Len(subStr) = 9 Or Len(subStr) = 12 Then
            'Replace number with mask
            str = Replace(str, subStr, maskTxt)
        End If
        'Reset substring
        subStr = ""
    End If
Next i

replcNum = str


End Function

Thanks.

How to replace text within string matching a pattern

How to replace text within string matching a pattern

Comment

Comment