Thank you Stephen, That may be useful for me for doing just the US bit. I have to device something that is for a bunch of countries, not just the US.. There is France, Germany, UK, Belgium, India, Bermuda, Australia, Argentina and PR among the other countries.. I may have missed a country or 2.. And whatever I device would need to be used with the AIE integration engine as that's what they have here for interchanging data.
Which is why I had posted an email earlier on trying to gather what is the general phone formats for these countries.. I got a response from Nyall about France which I found very useful.. Any users from these other countries who can share what their phone formats are like? Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Heider, Stephen Sent: Thursday, March 13, 2008 8:24 AM To: arslist@ARSLIST.ORG Subject: Re: Phone number formatting... ** 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___ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.21.7/1328 - Release Date: 3/13/2008 11:31 AM _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
