View Single Post
  #1 (permalink)  
Old June 13th, 2004, 03:23 AM
U.N.C.L.E. U.N.C.L.E. is offline
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default LIKE %<search text>%

I am performing a search of records by phone number...

SELECT * FROM tblClients
WHERE Phone LIKE '%" & Request("Phone") & "%'

The phone field is somewhat jumbled because everyone has different ways of entering their phone number into the db.

e.g.
1-800-555-1212
(800)555-1212
8005551212
18005551212

So when I enter a number to search, for example 555-1212, the query would return 2 rows. I'd like it to return all 4 rows.

Is there a method of 'stripping' the non-numeric chars from the column data for the purpose of the query comparison?

I'm thinking there may be some type of function I can use to modify the data, analogous to a regular expression: [^0-9]
Reply With Quote