Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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
0
IT Tech
Asked:
IT Tech
2 Solutions
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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