Request Help Removing "Special Characters" from fields 1

[expensive]

Hello

I'm struggling to work out how to remove all non-alphanumerical characters from a given field(s). I can do one at a time,

SELECT REPLACE(field, 'value to replace', 'what to replace with')
FROM tbl

But I need to do this for all non-alphanumerical characters, including spaces. Can anyone help?

 

[HarleyQuinn]

How about using a User Defined Function and Regular Expressions?

Put this code in a module:

Function StripString(strInput As String) As String
Dim objReg As Object

Set objReg = CreateObject("VBScript.RegExp")

With objReg
    .IgnoreCase = True
    .Multiline = False
    .Global = True
    .Pattern = "[^a-z|^0-9]"
    StripString = .Replace(strInput, "")
End With

Set objReg = Nothing

End Function

The pattern isn't the tightest it could be but it works for most.

Use this in your query like this:

SELECT StripString(MyField2) as AlphaNumeric
FROM MyTable;

If you need any explaination of the function just post back.

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AlexCuse]

Nice one HarleyQuinn. A lot of people forget that you can use regular expressions in Access that way

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[HarleyQuinn]

Cheers Alex

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[expensive]

Hi Harley

Thanks, that bit of VBA worked a charm.