asked on

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.

jamesgu

does those records show up when you do

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

Guy Hengel [angelIII / a3]

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.

ASKER CERTIFIED SOLUTION

CCongdon

membership

This solution is only available to members.

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

Start Free Trial

BrandonGalderisi

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?

CCongdon

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.

NO_CARRIER

ASKER

james: yes, they all show.

ccong: that works! thanks...

CCongdon

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.

jamesgu

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