Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfFormatUnformattedPhone]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfFormatUnformattedPhone]
GO

/***************************************************************************************/
-- This function takes a 10-char phone number (i.e., full phone number with area-code, 
-- but without any  "(" or ")" surrounding area-code, or dashes separating numbers),
-- and formats it to a "(xxx) xxx-xxx"  formatted layout.
-- This routine ASSUMES all 10-chars are filled in at the start!
/***************************************************************************************/
CREATE FUNCTION udfFormatUnformattedPhone
	(@cPhone	CHAR(10))
	RETURNS		CHAR(14)
AS
BEGIN
	RETURN '(' + SUBSTRING(@cPhone, 1, 3) + ') ' + 
               SUBSTRING(@cPhone, 4, 3) + '-' + 
               SUBSTRING(@cPhone, 7, 4) 
END