What's the fast way to do this?

Dr. Logic

Board Regular
Joined
Jul 13, 2005
Messages
218
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Code:
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 ....
 
Last edited:
Upvote 0
The regex approach shown will remove apostrophes as well? Might be a good thing, or maybe they want to keep the ' in O'Hara?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top