asked on

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

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

erikTsomik

ASKER

but the database have garbage data too i need to normalize the database side so the both sides are the same?

gdemaria

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

erikTsomik

ASKER

if I do it in the where clause will that be OK .

OR Replace(Replace(Replace(U.homePhone,'(', ''),')',''),'-','') LIKE '%3013400075%'

gdemaria

sure..

_agx_

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

gdemaria

Oh, I thought he meant the where clause of the column, not of the value... agree with agx