Results 1 to 4 of 4

Thread: What's the fast way to do this?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default What's the fast way to do this?

    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?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,807
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: What's the fast way to do this?

    Take a look at the first reply (mentioning UDF) here: https://stackoverflow.com/questions/...-in-access-sql
    That will only return numbers and letters, stripping all other characters out.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,475
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: What's the fast way to do this?

    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 by xenou; Aug 21st, 2019 at 11:31 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,703
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What's the fast way to do this?

    The regex approach shown will remove apostrophes as well? Might be a good thing, or maybe they want to keep the ' in O'Hara?
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •