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'.
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
I am using Coldfusion to pull the data., but not sure if I am doing it correctly
(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">
)
Asked:
4-
2
1 Solution
but the database have garbage data too i need to normalize the database side so the both sides are the same?
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
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
if I do it in the where clause will that be OK .
OR Replace(Replace(Replace(U.
OR Replace(Replace(Replace(U.
>> 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.
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.
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 Already a member? Login.
Featured Post
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.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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.