This function was written according to my needs , please modify to suit your requirements.

 

Input : VARCHAR(8000)

Output : VARCHAR(8000) [Formatted Phone Number]

 

Ex :

'8601234567' returns '(860)123-4567'

 

 

Tested on : SQL 2000,2005,2008

CREATE FUNCTION [dbo].[FormatPhoneNumber](@in VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
  BEGIN
      DECLARE @out AS VARCHAR(8000)
      DECLARE @counter AS INT
      DECLARE @outPos AS INT
      DECLARE @currentChar AS VARCHAR(1)

      SET @counter=1

      SELECT @out = ''

      WHILE @counter <= len(@in)
        BEGIN
            SET @currentChar=substring(@in, @counter, 1)

            IF ( isnumeric(@currentChar) = 1
                 AND @currentChar != '+'
                 AND @currentChar != '-'
                 AND @currentChar != '.' )
              BEGIN
                  IF ( ( len(@out) > 0 )
                        OR ( len(@out) = 0
                             AND @currentChar != '0' ) )
                    BEGIN
                        SET @out= @out + @currentChar;
                    END
              END

            SET @counter=@counter + 1
        END

      IF ( len(@out) = 10 )
        BEGIN
            SELECT @in = @out;

            SELECT @out = ''

            SET @counter=1

            WHILE @counter <= len(@in)
              BEGIN
                  SET @currentChar=substring(@in, @counter, 1)

                  BEGIN
                      IF ( @counter = 1 )
                        BEGIN
                            SET @out= '(';
                        END

                      IF ( @counter = 4 )
                        BEGIN
                            SET @out= @out + ')';
                        END

                      IF ( @counter = 7 )
                        BEGIN
                            SET @out= @out + '-';
                        END

                      BEGIN
                          SET @out= @out + @currentChar;
                      END
                  END

                  SET @counter=@counter + 1
              END
        END

      RETURN @out
  END