What's the fast way to do this?

[Dr. Logic]

Whats the fastest way to remove all the symbols in a column of data in access?
I only want the numbers and letters to be in my data.


My database is about 4 million records and one of the fields in the records is a catalog number and I want to remove all the symbols in the catalog number in all 4 million records. I can use an update query but it makes me run an update query for each symbol - that takes a long time.

Can an update query remove many symbols at the same time? What's the best way to do this?

 

[Joe4]

Take a look at the first reply (mentioning UDF) here: https://stackoverflow.com/questions/748674/how-to-replace-multiple-characters-in-access-sql
That will only return numbers and letters, stripping all other characters out.

 

[xenou]

I guess depending on how symbols are you talking about here I would use the regex solution but simple replace might be faster if you had only a small number of non-alphanumeric characters actually in question (hard to say without testing = probably also depends on the average length of the strings in each record).

It might help a little to not recreate the object on each function call though so:

Function CleanString(strText)
Static objRegEx As Object

If objRegEx Is Nothing Then
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.Global = True
    objRegEx.Pattern = "[^a-z0-9]"
End If

CleanString = objRegEx.Replace(strText, "")

End Function

EDIT:
Note that I am referring to the method in the link Joe gave ....

 

[Micron]

The regex approach shown will remove apostrophes as well? Might be a good thing, or maybe they want to keep the ' in O'Hara?