How to strip out non numeric characters in a query

#1 (**permalink**) 28-Jul-2006, 08:25 AM

I need to strip out all the non numeric characters
in a query. Please advise how to do this.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11936-how-strip-out-non-numeric-characters.html

*

#2 (**permalink**) 28-Jul-2006, 08:25 AM

Here is a function that will do this:

'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************

' ** This function strips all nonnumeric characters from a text string.

Function StripAllNonNumericChars(strOriginalString As String) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function

--

Ken Snell

"tom" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>I need to strip out all the non numeric characters
> in a query. Please advise how to do this.
>

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936

#3 (**permalink**) 28-Jul-2006, 08:25 AM

WOW!!!!!

That is a real mouthfull.

What I really need is the following:

substring(string from pattern)

Extract substring matching POSIX regular expression

"Ken Snell (MVP)" wrote in message news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
> Here is a function that will do this:
>
> '********************************
> '* *
> '* Fxn StripAllNonNumericChars *
> '* *
> '********************************
>
> ' ** This function strips all nonnumeric characters from a text string.
>
> Function StripAllNonNumericChars(strOriginalString As String) As String
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> Dim blnStrip As Boolean
> Dim intLoop As Integer
> Dim lngLoop As Long
> Dim strTemp As String, strChar As String
> On Error Resume Next
> For lngLoop = Len(strOriginalString) To 1 Step -1
> blnStrip = True
> strChar = Mid(strOriginalString, lngLoop, 1)
> For intLoop = Asc("0") To Asc("9")
> If strChar = Chr(intLoop) Then
> blnStrip = False
> Exit For
> End If
> Next intLoop
> If blnStrip = False Then strTemp = strChar & strTemp
> Next lngLoop
> StripAllNonNumericChars = strTemp
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> Exit Function
> End Function
>
>
> --
>
> Ken Snell
>
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
> >I need to strip out all the non numeric characters
> > in a query. Please advise how to do this.
> >
>
>

#4 (**permalink**) 28-Jul-2006, 08:25 AM

Unfortunately, I am not knowledgeable about REGEX and similar types of
pattern matching. John Nurick and Douglas Steele (both MVPs) are, however; I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
will "ping" them to take a look at this thread.

--

Ken Snell

"Tom" wrote in message
news:5uGdnV9vrtjbzQbZnZ2dnUVZ_rWdnZ2d@comcast.com. ..
> WOW!!!!!
>
> That is a real mouthfull.
>
> What I really need is the following:
>
> substring(string from pattern)
>
> Extract substring matching POSIX regular expression
>
>
> "Ken Snell (MVP)" wrote in message
> news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
>> Here is a function that will do this:
>>
>> '********************************
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>> '* *
>> '* Fxn StripAllNonNumericChars *
>> '* *
>> '********************************
>>
>> ' ** This function strips all nonnumeric characters from a text string.
>>
>> Function StripAllNonNumericChars(strOriginalString As String) As String
>> Dim blnStrip As Boolean
>> Dim intLoop As Integer
>> Dim lngLoop As Long
>> Dim strTemp As String, strChar As String
>> On Error Resume Next
>> For lngLoop = Len(strOriginalString) To 1 Step -1
>> blnStrip = True
>> strChar = Mid(strOriginalString, lngLoop, 1)
>> For intLoop = Asc("0") To Asc("9")
>> If strChar = Chr(intLoop) Then
>> blnStrip = False
>> Exit For
>> End If
>> Next intLoop
>> If blnStrip = False Then strTemp = strChar & strTemp
>> Next lngLoop
>> StripAllNonNumericChars = strTemp
>> Exit Function
>> End Function
>>
>>
>> --
>>
>> Ken Snell
>>
>>
>> "tom" wrote in message
>> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>> >I need to strip out all the non numeric characters
>> > in a query. Please advise how to do this.
>> >
>>
>>
>
>

#5 (**permalink**) 28-Jul-2006, 08:25 AM

Tom,

Firstly, add the following to a standard module:

Public Enum StripType
se_Char = &H1
se_Num = &H2
se_NonWord = &H4
se_Space = &H8
se_AllButChar = &H10
se_AllButNum = &H20
se_Custom = &H40
End Enum

Public Function StripEx(sText As String, lExpr As StripType, Optional
sUsrExpr As String = "") As String
'-------------------------------------------------------------------------------
' Function to selectively strip any combination of the following from a
string:
' Characters (a-z or A-Z)
' Numbers (0-9)
' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
' Spaces
'-------------------------------------------------------------------------------

Dim objRegEx As Object
Dim sRegExpr As String

Set objRegEx = CreateObject("VBScript.RegExp")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936

If lExpr And se_Custom Then
'User-defined expression
sRegExpr = sUsrExpr
ElseIf lExpr And se_AllButChar Then
'Strip out everything except alpha characters
sRegExpr = "[^a-zA-Z]"
ElseIf lExpr And se_AllButNum Then
'Strip out everything except numbers
sRegExpr = "\D"
Else
'Custom strip
If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
> 0, "|", "") & "\W"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936

With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With

Set objRegEx = Nothing
End Function

You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

"tom" wrote in message
news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>I need to strip out all the non numeric characters
> in a query. Please advise how to do this.
>

#6 (**permalink**) 28-Jul-2006, 08:25 AM

The code wouldnt compile

"Graham R Seach" wrote in message
news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
> Tom,
>
> Firstly, add the following to a standard module:
>
> Public Enum StripType
> se_Char = &H1
> se_Num = &H2
> se_NonWord = &H4
> se_Space = &H8
> se_AllButChar = &H10
> se_AllButNum = &H20
> se_Custom = &H40
> End Enum
>
> Public Function StripEx(sText As String, lExpr As StripType, Optional
> sUsrExpr As String = "") As String
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> '-------------------------------------------------------------------------------
> ' Function to selectively strip any combination of the following from a
> string:
> ' Characters (a-z or A-Z)
> ' Numbers (0-9)
> ' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
> ' Spaces
> '-------------------------------------------------------------------------------
>
> Dim objRegEx As Object
> Dim sRegExpr As String
>
> Set objRegEx = CreateObject("VBScript.RegExp")
>
> If lExpr And se_Custom Then
> 'User-defined expression
> sRegExpr = sUsrExpr
> ElseIf lExpr And se_AllButChar Then
> 'Strip out everything except alpha characters
> sRegExpr = "[^a-zA-Z]"
> ElseIf lExpr And se_AllButNum Then
> 'Strip out everything except numbers
> sRegExpr = "\D"
> Else
> 'Custom strip
> If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
> 0, "|", "") & "\d"
> If lExpr And se_NonWord Then sRegExpr = sRegExpr &
> IIf(Len(sRegExpr)
> > 0, "|", "") & "\W"
> If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
> > 0, "|", "") & "\s"
> End If
> 'Debug.Print "Regular Expression: " & sRegExpr
>
> With objRegEx
> .Pattern = sRegExpr
> .Global = True
> StripEx = .Replace(sText, "")
> End With
>
> Set objRegEx = Nothing
> End Function
>
> You can then call this function from your query:
> SELECT StripEx(myqueryfield, 32) As numbersonly
> FROM tblMyTable
>
> Regards,
> Graham R Seach
> Microsoft Access MVP
> Canberra, Australia
> ---------------------------
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>>I need to strip out all the non numeric characters
>> in a query. Please advise how to do this.
>>
>
>

#7 (**permalink**) 28-Jul-2006, 08:25 AM

Your code works but when I try to use it in a join
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?

"Ken Snell (MVP)" wrote in message
news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
> Here is a function that will do this:
>
> '********************************
> '* *
> '* Fxn StripAllNonNumericChars *
> '* *
> '********************************
>
> ' ** This function strips all nonnumeric characters from a text string.
>
> Function StripAllNonNumericChars(strOriginalString As String) As String
> Dim blnStrip As Boolean
> Dim intLoop As Integer
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> Dim lngLoop As Long
> Dim strTemp As String, strChar As String
> On Error Resume Next
> For lngLoop = Len(strOriginalString) To 1 Step -1
> blnStrip = True
> strChar = Mid(strOriginalString, lngLoop, 1)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> For intLoop = Asc("0") To Asc("9")
> If strChar = Chr(intLoop) Then
> blnStrip = False
> Exit For
> End If
> Next intLoop
> If blnStrip = False Then strTemp = strChar & strTemp
> Next lngLoop
> StripAllNonNumericChars = strTemp
> Exit Function
> End Function
>
>
> --
>
> Ken Snell
>
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>>I need to strip out all the non numeric characters
>> in a query. Please advise how to do this.
>>
>
>

#8 (**permalink**) 28-Jul-2006, 08:25 AM

Did you correct for the word-wrap in Graham's code?

The 3 lines of code

If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\W"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"

should each be one line each. Try the following instead:

If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"tom" wrote in message
news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy .net...
> The code wouldnt compile
>
>
> "Graham R Seach" wrote in message
> news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
> > Tom,
> >
> > Firstly, add the following to a standard module:
> >
> > Public Enum StripType
> > se_Char = &H1
> > se_Num = &H2
> > se_NonWord = &H4
> > se_Space = &H8
> > se_AllButChar = &H10
> > se_AllButNum = &H20
> > se_Custom = &H40
> > End Enum
> >
> > Public Function StripEx(sText As String, lExpr As StripType, Optional
> > sUsrExpr As String = "") As String
> >
'---------------------------------------------------------------------------
----
> > ' Function to selectively strip any combination of the following from a
> > string:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> > ' Characters (a-z or A-Z)
> > ' Numbers (0-9)
> > ' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
> > ' Spaces
> >
'---------------------------------------------------------------------------
----
> >
> > Dim objRegEx As Object
> > Dim sRegExpr As String
> >
> > Set objRegEx = CreateObject("VBScript.RegExp")
> >
> > If lExpr And se_Custom Then
> > 'User-defined expression
> > sRegExpr = sUsrExpr
> > ElseIf lExpr And se_AllButChar Then
> > 'Strip out everything except alpha characters
> > sRegExpr = "[^a-zA-Z]"
> > ElseIf lExpr And se_AllButNum Then
> > 'Strip out everything except numbers
> > sRegExpr = "\D"
> > Else
> > 'Custom strip
> > If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
> > If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
>
> > 0, "|", "") & "\d"
> > If lExpr And se_NonWord Then sRegExpr = sRegExpr &
> > IIf(Len(sRegExpr)
> > > 0, "|", "") & "\W"
> > If lExpr And se_Space Then sRegExpr = sRegExpr &
IIf(Len(sRegExpr)
> > > 0, "|", "") & "\s"
> > End If
> > 'Debug.Print "Regular Expression: " & sRegExpr
> >
> > With objRegEx
> > .Pattern = sRegExpr
> > .Global = True
> > StripEx = .Replace(sText, "")
> > End With
> >
> > Set objRegEx = Nothing
> > End Function
> >
> > You can then call this function from your query:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> > SELECT StripEx(myqueryfield, 32) As numbersonly
> > FROM tblMyTable
> >
> > Regards,
> > Graham R Seach
> > Microsoft Access MVP
> > Canberra, Australia
> > ---------------------------
> >
> > "tom" wrote in message
> > news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
> >>I need to strip out all the non numeric characters
> >> in a query. Please advise how to do this.
> >>
> >
> >
>
>

#9 (**permalink**) 28-Jul-2006, 08:25 AM

The problem is with the enum. I am using
Access 97. I am worried about upgrading for
fear of breaking the code I have there.

Is there problems with upgrading from Access 97.

"Douglas J Steele" wrote in message
news:e9800OvlGHA.3576@TK2MSFTNGP04.phx.gbl...
> Did you correct for the word-wrap in Graham's code?
>
> The 3 lines of code
>
> If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
> 0, "|", "") & "\d"
> If lExpr And se_NonWord Then sRegExpr = sRegExpr &
> IIf(Len(sRegExpr)
> 0, "|", "") & "\W"
> If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
> 0, "|", "") & "\s"
>
> should each be one line each. Try the following instead:
>
> If lExpr And se_Num Then
> sRegExpr = sRegExpr & _
> IIf(Len(sRegExpr) > 0, "|", "") & "\d"
> End If
> If lExpr And se_NonWord Then
> sRegExpr = sRegExpr & _
> IIf(Len(sRegExpr) > 0, "|", "") & "\W"
> End If
> If lExpr And se_Space Then
> sRegExpr = sRegExpr & _
> IIf(Len(sRegExpr) > 0, "|", "") & "\s"
> End If
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "tom" wrote in message
> news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy .net...
>> The code wouldnt compile
>>
>>
>> "Graham R Seach" wrote in message
>> news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
>> > Tom,
>> >
>> > Firstly, add the following to a standard module:
>> >
>> > Public Enum StripType
>> > se_Char = &H1
>> > se_Num = &H2
>> > se_NonWord = &H4
>> > se_Space = &H8
>> > se_AllButChar = &H10
>> > se_AllButNum = &H20
>> > se_Custom = &H40
>> > End Enum
>> >
>> > Public Function StripEx(sText As String, lExpr As StripType, Optional
>> > sUsrExpr As String = "") As String
>> >
> '---------------------------------------------------------------------------
> ----
>> > ' Function to selectively strip any combination of the following from a
>> > string:
>> > ' Characters (a-z or A-Z)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>> > ' Numbers (0-9)
>> > ' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z,
>> > 0-9)
>> > ' Spaces
>> >
> '---------------------------------------------------------------------------
> ----
>> >
>> > Dim objRegEx As Object
>> > Dim sRegExpr As String
>> >
>> > Set objRegEx = CreateObject("VBScript.RegExp")
>> >
>> > If lExpr And se_Custom Then
>> > 'User-defined expression
>> > sRegExpr = sUsrExpr
>> > ElseIf lExpr And se_AllButChar Then
>> > 'Strip out everything except alpha characters
>> > sRegExpr = "[^a-zA-Z]"
>> > ElseIf lExpr And se_AllButNum Then
>> > 'Strip out everything except numbers
>> > sRegExpr = "\D"
>> > Else
>> > 'Custom strip
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>> > If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
>> > If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
>>
>> > 0, "|", "") & "\d"
>> > If lExpr And se_NonWord Then sRegExpr = sRegExpr &
>> > IIf(Len(sRegExpr)
>> > > 0, "|", "") & "\W"
>> > If lExpr And se_Space Then sRegExpr = sRegExpr &
> IIf(Len(sRegExpr)
>> > > 0, "|", "") & "\s"
>> > End If
>> > 'Debug.Print "Regular Expression: " & sRegExpr
>> >
>> > With objRegEx
>> > .Pattern = sRegExpr
>> > .Global = True
>> > StripEx = .Replace(sText, "")
>> > End With
>> >
>> > Set objRegEx = Nothing
>> > End Function
>> >
>> > You can then call this function from your query:
>> > SELECT StripEx(myqueryfield, 32) As numbersonly
>> > FROM tblMyTable
>> >
>> > Regards,
>> > Graham R Seach
>> > Microsoft Access MVP
>> > Canberra, Australia
>> > ---------------------------
>> >
>> > "tom" wrote in message
>> > news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>> >>I need to strip out all the non numeric characters
>> >> in a query. Please advise how to do this.
>> >>
>> >
>> >
>>
>>
>
>