CODEWITHSUNDEEP
sql-serverstored-proceduressyntax
heb-NR
heb-NR

Reputation: 327

Changing all phone numbers SQL

I'm building a sample database in SQL Server 2008 R2 for a class and have filled it with dummy data I can create stored procedures and such with.

One of the sprocs I want to create is to change all the phone numbers (all American, 10 digit format) to look like (XXX)XXX-XXXX.

I've purposely entered a group of phone numbers in different fashions and want to have the sproc format them all into a standard format listed above so no matter what style the numbers are entered in the database, they get formatted properly.

I don't even know how to start this. I'm really still new to SQL. ALSO...I'm pulling all the phone numbers on the database, not from just one table.

    CREATE PROC spPhoneNumberFormat
AS

Select Employees.Phone, Vendors.Phone, Vendors.AltPhone
From Employees join Vendors on employees.ID = vendors.ID

Hope you all can help?

Upvotes: 1

Views: 6349

Answers (3)

Anthony A
Anthony A

Reputation: 26

In the version of SQL Server 2008 I have (r2) it seems the parameters of the substring function are actually (stringname, start location, length). Therefore the appropriate query to change 'XXXXXXXXXX' to '(XXX)XXX-XXXX' is:

update [TableName]

set [phone] = '('+substring([phone],1,3)+')'+substring([phone],4,3)+'-'+substring([phone],7,4)

where len([phone]) = 10

The qualifier (where len([phone]) = 10) will prevent altering any record with more than ten characters - which ensures your phone numbers won't be contaminated if you accidentally run the query twice in a row.

Upvotes: 1

Kahn
Kahn

Reputation: 1660

Since I've no idea which format American phone numbers appear in, I can only provide you with basic instructions on how to parse and handle strings.

You can use REPLACE(1,2,3) to parse the field into its basic form XXXXXXXXXX. In the replace, 1 is the full string you're handling, 2 is the part of which you want to replace, 3 the part you'll replace it with. So you can for example use REPLACE(PHONENUM,'+','') to remove plus signs. You can combine REPLACE functions or even use REGEX to some extent to parse multiple different characters, strings etc at the same time. Google will provide you with instructions on how.

When you have the phone number in format XXXXXXXXXX, you can use functions such as substring to reformat it. IE:

SELECT '('+SUBSTRING(PHONENUM,1,3)+')'+SUBSTRING(PHONENUM,4,6)+'-'+SUBSTRING(PHONENUM,7,LEN(PHONENUM))

Which would change XXXXXXXXXX into (XXX)XXX-XXXX.

Try and test those and you should get it working in no time at all. :)

Upvotes: 1

code save
code save

Reputation: 1106

Please try it

http://www.codeproject.com/Articles/20596/Telephone-Numbers-in-SQL-Server-2005-Part-2-Format

http://bytes.com/topic/sql-server/answers/705833-phone-number-conversion-xxxxxxxxx

http://www.sqlservercurry.com/2010/11/format-phone-numbers-in-sql-server.html

http://www.sqlservercentral.com/Forums/Topic698934-338-2.aspx

http://forums.devshed.com/ms-sql-development-95/format-phone-number-253468.html

http://www.katieandemil.com/t-sql-yyyymmdd

i hope

It is helpful to you

Upvotes: 0

Related Questions