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: [email protected]
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"
