Remove area code from telephone number

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]

ChazH
Starting Member

4 Posts

Posted - 2013-02-23 : 22:30:14

Thanks I will try it out!

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.

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),''),'-','');

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources