Función SQL para formatear números telefónicos.
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:
[sql]
SELECT dbo.FormatPhoneNumber(‘8601234567’)
–Devuelve (860) 123-4567
[/sql]
El script es compatible para SQL 2000, 2005, 2008
[sql]
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
[/sql]
