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

CREATE FUNCTION udfFormatUnformattedPhone
      (@cPhone      CHAR(10))
      RETURNS            CHAR(14)
AS
BEGIN
      RETURN '(' + SUBSTRING(@cPhone, 1, 3) + ') ' +
               SUBSTRING(@cPhone, 4, 3) + '-' +
               SUBSTRING(@cPhone, 7, 4)
END

this one is very nice

Create Function dbo.formatPhone(@phone varchar(30))
Returns varchar(30) As
Begin
	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
End
Go
Create Function dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
Returns varchar(500) AS
Begin
 
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')
 
	Return @myString
End
Go

Select allOpen 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!

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

THANKS A Ton!!!