Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Request Help Removing "Special Characters" from fields 1

Status
Not open for further replies.
expensive

expensive

Technical User
Oct 25, 2007
8
GB
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?
 
Sort by date Sort by votes
How about using a User Defined Function and Regular Expressions?

Put this code in a module:
Code: 
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:
Code: 
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Cheers Alex [smile]

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.
 
Upvote 0 Downvote
Hi Harley

Thanks, that bit of VBA worked a charm.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Locked
  • Question
Delete From Table
Replies
1
Views
284
Andrzejek
Andrzejek
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
542
lameid
lameid
TrekBiker
  • Locked
  • Question
Select addresses using two criteria, one 'Like" 2
Replies
3
Views
149
TrekBiker
TrekBiker
serino
  • Locked
  • Question
Help with SQL inner join statement 1
Replies
2
Views
142
serino
serino
MDGarcia
  • Locked
  • Question
Error Could not delete from specified tables 2
Replies
14
Views
440
MDGarcia
MDGarcia

Part and Inventory Search

Sponsor

Back
Top