Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

sql query with a where clause

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'),'&nbsp','','all'))#%" cfsqltype="cf_sql_varchar">
			  	  OR U.homePhone LIKE <cfqueryparam value="%#ucase(replace(replace(replace(replace(replace(trim(Request.phone),'.','','all'),'-','','all'),'(','','all'),')','','all'),'&nbsp','','all'))#%" cfsqltype="cf_sql_varchar">
				  OR U.workPhone LIKE <cfqueryparam value="%#ucase(replace(replace(replace(replace(replace(trim(Request.phone),'.','','all'),'-','','all'),'(','','all'),')','','all'),'&nbsp','','all'))#%" cfsqltype="cf_sql_varchar">
				  
				)

Open in new window

0
erikTsomik
Asked:
erikTsomik
  • 4
  • 2
1 Solution
 
gdemariaCommented:
I would strip out all non-numeric values from the value stored in the database and compare it with the same number-only search criteria.

Use this in coldfusion to reduce the search phone number to digits only:

   <cfset testPhone = rereplace(Request.phone,"[^[:digit:]]*","","all")>

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.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
but the database have garbage data too i need to normalize the database side so the both sides are the same?
0
 
gdemariaCommented:
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.

something like this:

   select  stuff(workPhone, 1, patindex('%[0-9]%', workPhone)-1, '')

http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters

https://www.google.com/search?q=sql+server+strip+non-numeric+characters+from+string&ie=utf-8&oe=utf-8
0
Easily Design & Build Your Next Website

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'.

 
erikTsomikSystem Architect, CF programmer Author Commented:
if I do it in the where clause will that be OK .

 OR Replace(Replace(Replace(U.homePhone,'(', ''),')',''),'-','') LIKE '%3013400075%'
0
 
gdemariaCommented:
sure..
0
 
_agx_Commented:
>> 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.
0
 
gdemariaCommented:
Oh, I thought he meant the where clause of the column, not of the value... agree with agx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now