Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Remove area code from telephone number

Author  Topic 

ChazH
Starting Member

4 Posts

Posted - 2013-02-23 : 13:29:28

Hi All,

New to SQL Server and new to the forum!

How would I go about removing the area code from a customer table?

The column is nvarchar(30) numbers are stored as (345)111-1111 and I want to reformat to 1111111.

Thanks in advance!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-23 : 17:56:30
[code]REPLACE(STUFF(PhoneNumberColumn,1,CHARINDEX(')',PhoneNumberColumn),''),'-','')[/code]
Go to Top of Page

ChazH
Starting Member

4 Posts

Posted - 2013-02-23 : 22:30:14
Thanks I will try it out!
Go to Top of Page

ChazH
Starting Member

4 Posts

Posted - 2013-02-25 : 16:59:18
Thanks again for your help but I was not able to figure out the format for this command.

The table is dbo.customer and the column name is customer.phonenumber. I tried a bunch of different things but could not get it to work on my test database.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 17:22:54
If you want to keep the data in the table as it is and remove the area code in a select, do this:

SELECT REPLACE(STUFF(phonenumber,1,CHARINDEX(')',phonenumber),''),'-','') FROM dbo.customer
If you want to permanently change the table by updating it to remove the area code, do this:
UPDATE dbo.customer SET
phonenumber = REPLACE(STUFF(phonenumber,1,CHARINDEX(')',phonenumber),''),'-','');
Go to Top of Page

ChazH
Starting Member

4 Posts

Posted - 2013-02-25 : 18:25:05
Thanks! I will learn the syntax tonight and try it out on my test server tomorrow.
Go to Top of Page
   

- Advertisement -