El rincón de JMACOE

Esta función está escrita de acuerdo a mis necesidades, por favor, modificala si crees conveniente para adaptarla a tus necesidades.

• Entrada: VARCHAR (8000)
• Salida: VARCHAR (8000) [Número de teléfono formateado]

Por ejemplo:

SELECT dbo.FormatPhoneNumber('8601234567')
--Devuelve  (860) 123-4567

El script es compatible para 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