0 Replies - 1451 Views - Last Post: 18 April 2008 - 05:31 AM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

User defined function for formatting US phone numbers in MSSQL

Posted 18 April 2008 - 05:31 AM

Description: Simply pass the function the value you wish to have formattedThis is a user defined function for formatting US phone number in Microsoft SQL Server
CREATE FUNCTION dbo.udfFormatPhoneNumber(@PhoneNumber NVARCHAR(60))
RETURNS NVARCHAR(60)
AS
  BEGIN
	DECLARE @Length INT 
	DECLARE @TempStr NVARCHAR(30)
	--Strip all non-numeric characters from string
	WHILE PATINDEX('%[^0-9]%', @PhoneNumber) > 0 
        	SET @PhoneNumber = REPLACE(@PhoneNumber, SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%', @PhoneNumber),1),'') 
		SET @Length = LEN(@PhoneNumber)-10
		--Remove a leading "1" if exists (15555551212)
		IF SUBSTRING(@PhoneNumber,1,1) ='1' and LEN(@PhoneNumber) >10
			BEGIN
				SET @PhoneNumber=SUBSTRING(@PhoneNumber,2,LEN(@PhoneNumber) -1)
			END	
		--Format our phone number to the (555) 555-1212 format
		SET @TempStr = @PhoneNumber
		SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3)+ ')' + SUBSTRING(@PhoneNumber,4,3) + '-'+ SUBSTRING(@PhoneNumber,7,4)
-- Returns Value 
    RETURN @PhoneNumber
END


//Example
--this
select dbo.udfFormatPhoneNumber('1(555)5551212')
--will return (555) 555-1212


Is This A Good Question/Topic? 0
  • +

Page 1 of 1