Joe,
Here is a SQL Server function that can be used to format your phone numbers. It works with US 7 and 10 digit phone numbers and 10 digit phone numbers with extensions. I like using a function because you can call it with one line of code in ARS workflow, or from a SQL-based database (ARS or non-ARS), and also from a SQL prompt. HTH CREATE FUNCTION dbo.udf_FormatPhoneNumber ( @Phone VARCHAR(100) ) RETURNS VARCHAR(30) AS BEGIN IF ISNULL(@Phone ,'') = '' RETURN NULL DECLARE @tmpPhone VARCHAR(30), @ReturnValue VARCHAR(30), @Pos INT, @Character VARCHAR(1), @Ext VARCHAR(10) -- Remove any existing formatting. SET @tmpPhone = REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@Phone, ''), '(', '') , ')', ''), '-', ''), ' ', '') set @ReturnValue = '' SET @Pos = 1 -- Build string of only numbers. WHILE @Pos <= LEN(@tmpPhone) BEGIN SET @Character = SUBSTRING(@tmpPhone, @Pos, 1) IF @Character BETWEEN '0' AND '9' SET @ReturnValue = @ReturnValue + @Character SET @Pos = @Pos + 1 END IF @ReturnValue = '' RETURN NULL IF LEN(@ReturnValue) > 10 SET @Ext = ' x' + SUBSTRING(@ReturnValue, 11, 10) ELSE SET @Ext = '' IF LEN(@ReturnValue) >= 10 SET @ReturnValue = RTRIM('(' + LEFT(@ReturnValue, 3) + ') ' + SUBSTRING(@ReturnValue, 4, 3) + '-' + SUBSTRING(@ReturnValue, 7, 4) + ' ' + @Ext) ELSE SET @ReturnValue = RTRIM(LEFT(@ReturnValue, 3) + '-' + SUBSTRING(@ReturnValue, 4, 4) + @Ext) RETURN @ReturnValue END Stephen Remedy Skilled Professional ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Wednesday, March 12, 2008 10:20 PM To: arslist@ARSLIST.ORG Subject: Phone number formatting... <snipped to size for arslist> Joe D'Souza __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"