We help IT Professionals succeed at work.

I need a function written in SQL server to format the Ph.no stored in the database??

Last Modified: 2012-05-05
I need a function written in SQL server2005  to format the Ph.no stored in the SQL server database in the format (xxx)-xx-xxxx. (It should include the ext if it is present)
Note: The Ph.no stored in the database are mostly of the format xxx/xxx-xxxx or
xxx-xxx-xxxx ext xxxx

Can anyone please help in creating this function!

CREATE FUNCTION dbo.fn_PhoneFormat
      @Phone as VARCHAR(50)
-- format the phone in the format (999)999-9999
-- code to convert the Ph no format
      RETURN @Phone


Watch Question

CREATE FUNCTION udfFormatUnformattedPhone
      (@cPhone      CHAR(10))
      RETURNS            CHAR(14)
      RETURN '(' + SUBSTRING(@cPhone, 1, 3) + ') ' +
               SUBSTRING(@cPhone, 4, 3) + '-' +
               SUBSTRING(@cPhone, 7, 4)
this one is very nice
Create Function dbo.formatPhone(@phone varchar(30))
Returns varchar(30) As
	Declare @rtnValue varchar(30)
	Set @Phone = dbo.getCharacters(@Phone,'0-9')
	Set @rtnValue = replace(case 
			when len(@phone) > 10 
				then stuff(stuff(stuff(stuff(@phone,11,0,' x'),7,0,'-'),4,0,') '),1,0,'(')
			when len(@phone) = 10 
				then stuff(stuff(stuff(@phone,7,0,'-'),4,0,') '),1,0,'(')
			else @phone end,'(000) ','')
	Return @rtnValue
Create Function dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
Returns varchar(500) AS
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')
	Return @myString

Open in new window


Thank You very much . Actually heres the more detailed description of my problem. I have shown the original data in the contact column in TableA table in SQLServer on the left side and the data i have to get by using this function dbo.fn_PhoneFormat on the right side.

CONTACT Column in TableA                         dbo.fn_PhoneFormat
999/999-9999                                  ------> (999)999-9999
999-999-9999                                 ------> (999)999-9999
abc@abc.com                               ------>  abc@abc.com
999-999-9999 Florida                       ------> (999)999-9999 Florida
999-999-9999 or 1234                     ------> (999)999-9999 or 1234
999-999-9999 X 123                      ------> (999)999-9999 x 123
999-999-9999 x 1234 (Mr.John)         ------> (999)999-9999 x1234 (Mr.John)
999-999-9999-Angie                     ------> (999)999-9999-Angie
999-999-9999 xt.1234                     ------> (999)999-9999 xt.1234
999-999-9999 Ext.1234                    ------> (999)999-9999 Ext.1234


Basically, if there is any letters or characters after the 10-digit number .....it shud also print without removing it. Becoz sometimes this contact column in TableA can also contain email-id or ext or name of the person.

This is doing the right thing or am I missing someting?

For Example :

999-999-9999 Florida                       ------> (999)999-9999 Florida
999-999-9999 or 1234                     ------> (999)999-9999 or 1234

You quote : "Basically, if there is any letters or characters after the 10-digit number .....it shud also print without removing it."

In the above example it does exxaclty this, or please if I missunderstand do provide the third column on above example, how you would like to be the result.


Hi bashka_abdyli,
                                 Excellent. Thank you so much for the solution. It works perfect. But that CONTACT column in TABLE A ....also sometimes contains the email id's of people instead of their Ph.no.  So,In that case it doesnt need to do anything except it should just print the email id whatever is in the column ...just as it is.

So....it is not doing this one.

abc123@abc.com                               ------>  abc123@abc.com
999-999-9999 x 1234 (Mr.John)         ------> (999)999-9999 x1234 (Mr.John)

So the logic maybe something like ...like if their is a continuos 10-digit number then it should format it as (999)999-9999 and then print everything else just as it is from the original CONTACT column in TABLE A.

Thanks in advance!

This one is on us!
(Get your first solution completely free - no credit card required)


bashka_abdyli ......THANK YOU VERY MUCH!!! That was really very very helpful!! Thanks!:)



Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.