• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

Remove hyphens from column in SQL (Replace Instr)?

I have a column with inconsistent data.  Some fields have hyphens in the phone numbers, some do not.  I want to remove any hyphens from the column using SQL.

I've tried UPDATE MyTable SET [PhoneNumber] = Replace([PhoneNumber],"-","") ... and also with chr(45) instead... neither work.

0
NO_CARRIER
Asked:
NO_CARRIER
1 Solution
 
jamesguCommented:
does those records show up when you do

select * from <your_table> where PhoneNumber like '*-*',


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
maybe it is not -, but ? ... you don't see the difference, but you might want to use ASC() function to find out the ascii code of the character you pass.
0
 
CCongdonCommented:
I'm not sure with Access, but I know in SQL Server the REPLACE function needs single quotes not double quotes to set your strings with.
Replace([PhoneNumber],'-','')  
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
BrandonGalderisiCommented:
UPDATE MyTable SET [PhoneNumber] = Replace([PhoneNumber],"-","") is correct.

Perhaps the phone number isn't stored but it is applied as part of some output mask?
0
 
CCongdonCommented:
Well, if double-quotes is correct for Access, then I'd go with Angelll's suggestion and check to see if it is a CHR(45) in there.
0
 
NO_CARRIERAuthor Commented:
james: yes, they all show.

ccong: that works! thanks...
0
 
CCongdonCommented:
OK, well, the reason I mentioned the single quote thing is that I just ran into that problem on SQL 2005 yesterday, and that was what I finally figured out I was doing wrong. For the SQL server, the double quotes seemed to be trying to make the server do something with the table (I was getting the error Invalid Column Name ".") Glad to have been of help.
0
 
jamesguCommented:
the string in double quotes is considered as a column name in sql server,

thought double quotes works as single quotes in access as BrandonGalderis. strange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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