Regular Expressions in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I couldn't find any documentation concerning this topic.
I want to put a regular expression in an SQL query under Access. As for an
example, under Oracle, one can say:
"SELECT TelNumber FROM ... WHERE ... REGEXP_LIKE="[0-9]" ".
Give me all Telephone Numbers that only consist of a number between 0 and 9
(I know, this doesn't make sense! :o) )
How can this be done in Access?

Thank you for your answers.

Ciao,
Mel
 
Mel_la_Bel said:
I couldn't find any documentation concerning this topic.
I want to put a regular expression in an SQL query under Access. As for an
example, under Oracle, one can say:
"SELECT TelNumber FROM ... WHERE ... REGEXP_LIKE="[0-9]" ".
Give me all Telephone Numbers that only consist of a number between 0 and 9
(I know, this doesn't make sense! :o) )
Click to expand...


Use the Microsoft vbScript Regular Expression library. I
don't know where you can find the Help file for this
library.
 
Perhaps you mean you are trying to find all phone numbers that have only number
characters in them. You should be able to use criteria like

NOT Like "*[!0-9]*


Or if your phone number were always 10 number characters.

Like "##########"



Marshall said:
Mel_la_Bel said:
I couldn't find any documentation concerning this topic.
I want to put a regular expression in an SQL query under Access. As for an
example, under Oracle, one can say:
"SELECT TelNumber FROM ... WHERE ... REGEXP_LIKE="[0-9]" ".
Give me all Telephone Numbers that only consist of a number between 0 and 9
(I know, this doesn't make sense! :o) )
Click to expand...

Use the Microsoft vbScript Regular Expression library. I
don't know where you can find the Help file for this
library.
Click to expand...
 
I wrote about using regular expressions in Access in my October, 2003
"Access Answers" column for Pinnacle Publication's "Smart Access", but
unfortunately I can't remember whether I covered using regular expressions
in queries.

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html
 
Thanks to all for your help.

I managed it this way:
Public Function IsAlphaNumeric(TestString As Variant) As Boolean

Dim sTemp As String
Dim iLen As Integer
Dim iCtr As Integer
Dim sChar As String

'returns true if all characters in a string are alphabetical or numeric
'returns false otherwise or for empty string

If Not IsNull(TestString) Then
sTemp = TestString
iLen = Len(sTemp)
If iLen > 0 Then
For iCtr = 1 To iLen
sChar = Mid(sTemp, iCtr, 1)
If Not sChar Like "[0-9A-Za-z]" Then Exit Function
Next

IsAlphaNumeric = True
End If
End If
End Function

Takes very long, but it works.

Ciao,
Mel

Douglas J. Steele said:
I wrote about using regular expressions in Access in my October, 2003
"Access Answers" column for Pinnacle Publication's "Smart Access", but
unfortunately I can't remember whether I covered using regular expressions
in queries.

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Mel_la_Bel said:
Hi all,

I couldn't find any documentation concerning this topic.
I want to put a regular expression in an SQL query under Access. As for an
example, under Oracle, one can say:
"SELECT TelNumber FROM ... WHERE ... REGEXP_LIKE="[0-9]" ".
Give me all Telephone Numbers that only consist of a number between 0 and
9
(I know, this doesn't make sense! :o) )
How can this be done in Access?

Thank you for your answers.

Ciao,
Mel
Click to expand...
Click to expand...
 
Back
Top