Having an issue with the MSSQL select statement using the replace function

Hi All,

   I'm trying to do a select statement in MSSQL 2012 and PHP 5.3 from a column filled with telephone numbers.  Some have spaces in between, some have hyphens, some have none.  I tried the following select statement:

select *, replace(dbo.MERCHANT.CONTACT_PHONE, '-', '') as CTPHONE from dbo.MERCHANT where CONTACT_PHONE = '7781234567'

   But it doesn't return any rows.  I've tried inside the SQL Server Management Studio as well, but no rows are returned.

   I've tried googling but I can't seem to find any help there.

Thanks!!
Zack
IT TechAsked:
Who is Participating?
 
Simone BSenior E-Commerce AnalystCommented:
The CONTACT_PHONE still has dashes in it, so your Where clause won't be satisfied.

Try this:

select *, replace(dbo.MERCHANT.CONTACT_PHONE, '-', '') as CTPHONE from dbo.MERCHANT where replace(dbo.MERCHANT.CONTACT_PHONE, '-', '') = '7781234567'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>select *, replace(dbo.MERCHANT.CONTACT_PHONE, '-', '') as CTPHONE from dbo.MERCHANT where CONTACT_PHONE = '7781234567'

The SQL query engine processes the SELECT clause of a query dead last, so if you have a row WHERE CONTACT_PHONE='778-123-4567', then you'll have to search for it with all of the dashes/underscore/whatever, regardless of what the replace() does in the SELECT.

Otherwise, if you want to avoid that, then throw it into a subquery.   Something like..

SELECT a.*, a.CTPHONE
FROM (
   select *, replace(dbo.MERCHANT.CONTACT_PHONE, '-', '') as CTPHONE
   from dbo.MERCHANT ) a
where a.CONTACT_PHONE = '7781234567'
0
 
IT TechAuthor Commented:
Thanks very much!!!
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.

All Courses

From novice to tech pro — start learning today.