Question about a nested select query using RIGHT and REPLACE

[Korizon67]

I have a database that grabs input from a website. The form on the website has poor validation so I get various types of entries for phone number. some of these formats.. 1 (222) 333-4444, 12223334444 1.222.333.4444 1/222/333/4444 .. you know... That Ol' chestnut!! I have even seen 1=222=333=4444 LOL ppl are crazy!

What I need to accomplish is, I need to build a query for my search engine that queries a = or LIKE phone.. I have one that works but there has to be a simple command I am missing that would replace my rediculous nest lol. My SELECT is as follows

Also could someone let me know how to insert code to this forum please?

SELECT * FROM contact WHERE (SELECT RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contact_prim_phone,"(",""),")", "" ), " ", "" ),"-",""),"+",""),10)) LIKE '$phone'

Like I said ... this seems to work fine but i am not sure if there is a quicker command to do it? like one that strips all non numeric chars, similar to the PHP

preg_replace("/[^0-9]/", "", $phone)

As it is i need to add lots more REPLACEs to cover all possible entries

Thank You

Mike

 

[audiopro]

Would it not be prudent to reformat all the phone entries in the database. Remove all non numeric chars from the field and just leave the raw number.


Adding code to the web site
Use the following syntax but without spaces between chars

[ c o d e ]

Code here

[ / c o d e ]



Keith

http://www.studiosoft.co.uk

 

[r937]

best practice: use two columns, one for the formatted value, e.g. (937)123-4576 or +49-69-89554321, and one for the stripped-down numerics-only number

r937.com | rudy.ca

 

[Korizon67]

I spose I could do that, but its kinda risky to mess with a major query that would change over 500,000 phone numbers on a system that is running 24/7 with over 75 users. Really all I wanted to know is... is there a function like php..

preg_replace("/[^0-9]/", "", $phone)

For Mysql

Thanks for the replies.

Mike

 

[r937]

no there isn't, REGEXP only matches

so you will have to bite the bullet and nest as many REPLACEs as you need



r937.com | rudy.ca

 

[Korizon67]

Ok, thank you much

Mike