Function Used to Reformat Phone Numbers

,

Can use this to select , insert and update phone fields into a standard format.

Select FormatPhone(phone_column) from table

Update Table
set phone_column=FormatPhone(phone_column)
Where ...

Insert Into Table ( column1, phone_primary ...)
Values ( 'qwerty', FormatPhone(1455846677)

And the like --

/****** Object:  UserDefinedFunction [dbo].[FormatPhone]    Script Date: 12/01/2006 11:05:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FormatPhone](
@phone NVARCHAR(60)
)
RETURNS NVARCHAR(60)
AS
  BEGIN
-- Strip @number of extra chracters
	DECLARE @lenPhone INT ,@phoneStr NVARCHAR(30)
	WHILE PATINDEX('%[^0-9]%', @phone) > 0 
        SET @phone = REPLACE(@phone, 
            SUBSTRING(@phone,PATINDEX('%[^0-9]%', @phone),1),'') 
	-- Format as 000-000-0000
		SET @lenPhone = LEN(@phone)-10
-- Remove Leading 1 
	IF SUBSTRING(@phone,1,1) ='1' and LEN(@phone) >10
		BEGIN
			SET @phone=SUBSTRING(@phone,2,LEN(@phone)-1)
		END	
--	Format @phone string to 000-000-0000 ext. 0000
		SET @phoneStr=@phone
		SET	@phone = SUBSTRING(@phone,1,3)+
			'-' + SUBSTRING(@phone,4,3) +
			'-'+ SUBSTRING(@phone,7,4)
-- ADD Extension
	IF @lenPhone > 1 
		SET @phone=@phone +' ext: '+ SUBSTRING(@phoneStr,11,@lenPhone)
-- Returns Value 
    RETURN @phone
END

Rate

Share

Share

Rate