Replace characters in one column at once? (1 Viewer)

Morten · May 31, 2013

I need to trim my supplier item number for certain characters and spaces in a table with more than 100.000 records.

Table: Katalog_aabent
Column: Leverandoerdelnummer
Characters: ."&,-<>*?/\:;_'() and spaces

How can I replace/trim my supplier item number at once in a VBA function?

pr2-eugin · May 31, 2013

A custom function will be in order.. However some sample data would help us understand/provide an appropriate answer..

jdraw · May 31, 2013

If you use that item number in any relationships, I think you'll be in for some real pain.

If you are positive that the item number with these non alphanumeric characters is not used to relate tables, then a function should work.

In my view you will still have to process the field character by character.

Morten · Jun 4, 2013

pr2-eugin said:
A custom function will be in order.. However some sample data would help us understand/provide an appropriate answer..

I have uploaded a sample file, so you can see the data. I think the best solution would be to clean the 'Supplier_item_number' in a new column, so I don't change in the original 'Supplier_item_number'.

Morten · Jun 4, 2013

jdraw said:
If you use that item number in any relationships, I think you'll be in for some real pain.

If you are positive that the item number with these non alphanumeric characters is not used to relate tables, then a function should work.

In my view you will still have to process the field character by character.

Thank you for your reply.

I don't use the 'Supplier_item_number' in any relationships, but I think the best would be to keep the original data and then clean the 'supplier_item_number' in a new column.

pr2-eugin · Jun 4, 2013

Finally

Create a Query.. Something like..

Code:

SELECT tblMdm.Id, tblMdm.Supplier_name, [COLOR=Red][B]removeChars[/B][/COLOR]([Supplier_item_number]) AS newSupID, tblMdm.Supplier_item_number
FROM tblMdm;

Where removeChars is a User defined function.. Along the lines of..

Code:

Public Function removeChars(tmpStr As String) As String
[COLOR=Green]'*******************************************************
'       Code to Remove special Characters, defined
'          in an Array from an argument String.
'
' USAGE Exmaple:
'   ? removeChars("(103A09) 103-A-09")
'     103A09 103A09
'
'Code Courtesy of
' Paul Eugin
'*******************************************************[/COLOR]
    Dim iCtr As Integer, splCharsArr(16) As String

    splCharsArr(0) = "."
    splCharsArr(1) = """"
    splCharsArr(2) = "&"
    splCharsArr(3) = ","
    splCharsArr(4) = "-"
    splCharsArr(5) = "<"
    splCharsArr(6) = ">"
    splCharsArr(7) = "*"
    splCharsArr(8) = "?"
    splCharsArr(9) = "/"
    splCharsArr(10) = "\"
    splCharsArr(11) = ":"
    splCharsArr(12) = ";"
    splCharsArr(13) = "_"
    splCharsArr(14) = "'"
    splCharsArr(15) = "("
    splCharsArr(16) = ")"
    
    For iCtr = 0 To 16
        tmpStr = Replace(tmpStr, splCharsArr(iCtr), vbNullString)
    Next
    removeChars = Trim(tmpStr)
End Function

Copy the above code into a module, save it by giving a different name other than removeChar.. Save and Compile it.. Then run the Query..

stopher · Jun 4, 2013

How about:

Code:

Public Function clean(strInput As String) As String

Dim rgx As New RegExp

rgx.Pattern = "[\.\""\&&\,\-\<\>\*\?\/\\\:\;\_\'\(\)]"
rgx.Global = True
clean = rgx.Replace(strInput, "")

End Function

To run the above the refence to Microsoft VBScript Regular Expressions needs to be added.

pr2-eugin · Jun 4, 2013

So neat.. I never have used, TBH never even knew VBA had RegEx.. Learn something new everyday.. Thank you stopher..

Morten · Jun 4, 2013

pr2-eugin said:

Finally

Create a Query.. Something like..

Code:

SELECT tblMdm.Id, tblMdm.Supplier_name, [COLOR=red][B]removeChars[/B][/COLOR]([Supplier_item_number]) AS newSupID, tblMdm.Supplier_item_number
FROM tblMdm;

Where removeChars is a User defined function.. Along the lines of..

Code:

Public Function removeChars(tmpStr As String) As String
[COLOR=green]'*******************************************************[/COLOR]
[COLOR=green]'       Code to Remove special Characters, defined[/COLOR]
[COLOR=green]'          in an Array from an argument String.[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=green]' USAGE Exmaple:[/COLOR]
[COLOR=green]'   ? removeChars("(103A09) 103-A-09")[/COLOR]
[COLOR=green]'     103A09 103A09[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=green]'Code Courtesy of[/COLOR]
[COLOR=green]' Paul Eugin[/COLOR]
[COLOR=green]'*******************************************************[/COLOR]
    Dim iCtr As Integer, splCharsArr(16) As String
 
    splCharsArr(0) = "."
    splCharsArr(1) = """"
    splCharsArr(2) = "&"
    splCharsArr(3) = ","
    splCharsArr(4) = "-"
    splCharsArr(5) = "<"
    splCharsArr(6) = ">"
    splCharsArr(7) = "*"
    splCharsArr(8) = "?"
    splCharsArr(9) = "/"
    splCharsArr(10) = "\"
    splCharsArr(11) = ":"
    splCharsArr(12) = ";"
    splCharsArr(13) = "_"
    splCharsArr(14) = "'"
    splCharsArr(15) = "("
    splCharsArr(16) = ")"
 
    For iCtr = 0 To 16
        tmpStr = Replace(tmpStr, splCharsArr(iCtr), vbNullString)
    Next
    removeChars = Trim(tmpStr)
End Function

Copy the above code into a module, save it by giving a different name other than removeChar.. Save and Compile it.. Then run the Query..

Thank you it works perfect.

Morten · Jun 4, 2013

I forgot about leading zeros. Can this be build in to the function?

Replace characters in one column at once? (1 Viewer)

Morten

Registered User.

pr2-eugin

Super Moderator

jdraw

Super Moderator

Morten

Registered User.

Attachments

Morten

Registered User.

pr2-eugin

Super Moderator

stopher

AWF VIP

pr2-eugin

Super Moderator

Morten

Registered User.

Morten

Registered User.

Similar threads

Users who are viewing this thread