Monday, November 23, 2009

SQL Stored Procedure for Number Format

CREATE FUNCTION [dbo].[fnFormatPhoneNumber](@PhoneNo VARCHAR(20))
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @Formatted VARCHAR(25)

IF (LEN(@PhoneNo) >= 10)
SET @Formatted = LEFT(@PhoneNo, 3) + '.' + SUBSTRING(@PhoneNo, 4, 3) + '.' + SUBSTRING(@PhoneNo, 7, 4)
ELSE
SET @Formatted = @PhoneNo


RETURN @Formatted
END
GO
Use it as follows:
SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber FROM SomeTable

No comments: