I have a problem where I trying to get all people with given phone number. THe problem is the phone numbers are inconsistently stored in the databse. For example,
(111)111-1111,111.111.1111, 111-111-1111
I am using Coldfusion to pull the data., but not sure if I am doing it correctly
AND (U.cellPhone LIKE <cfqueryparam value="%#ucase(replace(replace(replace(replace(replace(trim(Request.phone),'.','','all'),'-','','all'),'(','','all'),')','','all'),' ','','all'))#%" cfsqltype="cf_sql_varchar"> OR U.homePhone LIKE <cfqueryparam value="%#ucase(replace(replace(replace(replace(replace(trim(Request.phone),'.','','all'),'-','','all'),'(','','all'),')','','all'),' ','','all'))#%" cfsqltype="cf_sql_varchar"> OR U.workPhone LIKE <cfqueryparam value="%#ucase(replace(replace(replace(replace(replace(trim(Request.phone),'.','','all'),'-','','all'),'(','','all'),')','','all'),' ','','all'))#%" cfsqltype="cf_sql_varchar"> )
Then compare this number with the database phone number all stripped down to numbers only. All you need is to find the same type of replace for your particular database.
Yes, as I said, you need to wrap the column in a replace function just like the CF one to strip out all non-numeric values, then you can compare apples to apples.
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
>> if I do it in the where clause will that be OK .
Technically there's wrong with it, but it's better for performance if you don't. Using functions that way will force a table/index scan (essentially the same thing). Instead, store the unformatted numbers in the db as GD originally suggested.
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Use this in coldfusion to reduce the search phone number to digits only:
<cfset testPhone = rereplace(Request.phone,"[
Then compare this number with the database phone number all stripped down to numbers only. All you need is to find the same type of replace for your particular database.