/*
Select top 50 
	[DBValue]	= phonenumber
	,[formatted] 	= dbo.fnPhonePart('formatted', phonenumber)
	,countryPart 	= dbo.fnPhonePart('country', phonenumber)
	,areaCodePart	= dbo.fnPhonePart('area', phonenumber)
	,localPart 	= dbo.fnPhonePart('local', phonenumber)
	,extPart	= dbo.fnPhonePart('ext', phonenumber)
	,Stripped	= dbo.fnPhonePart('stripped', phonenumber)
from 	PhoneNumber_t
where	len(phoneNumber) > 10
*/

If Object_ID('dbo.fnPhonePart') > 0
	Drop Function dbo.fnPhonePart
GO

Create Function dbo.fnPhonePart( @part varchar(10), @phone varChar(50) )
returns varChar(50)
as
Begin
	/*
	this function behaves similarly to the Sql function: DateName.
	
	returns a string representing a phonenumber part
	OR a fully formatted phonenumber.

	possible phone parts are:
		international	(anything that begins with 'i' or 'c')
		areaCode	(anything that begins with 'a')
		local		(anything that begins with 'l')
		Extension	(anything that begins with 'e' or 'x')
		FullyFormated	(anything that begins with 'f')
		Stripped	(anything that begins with 's'
				Only numbers - no extension, no formatting)
	*/

	declare @retVal varchar(50)

	declare @num varChar(50)
		,@number varchar(50)
		,@x varChar(50)
		,@i int
		,@c char(1)

		,@country varchar(50)
		,@area varchar(50)
		,@local varchar(50)
		,@ext varchar(50)

	--Seperate number from extension
	--assumes extension will be signified by 'X'

	--number w/o extension
	if(len(isNull(@phone,'')) > 0)
		Set @num = substring(@phone, 1, isNull(nullif(charindex('X', @phone)-1, -1), len(@phone)))
	else
		set @num = ''

	--extension
	if(charindex('X', @phone) > 0)
		Set @ext = subString(@phone, charindex('X', @phone) + 1, len(@phone) - charindex('X',@phone))
	else
		set @ext = cast(null as varChar(15))
	

	--strip nonNumeric characters from @num
	Select @i = 1, @number = ''
	while(@i <= len(@num))
	Begin
		set @c = subString(@num, @i, 1)
		if( @c IN ('0','1','2','3','4','5','6','7','8','9') )
	            Set @number = @number + @c
	
		Set @i = @i + 1			
	End

	if(len(@number) = 10)
	Begin
		--format like: 1-999-999-9999
		Select	@country = '1'
			,@area = subString(@number, 1, 3)
			,@local = subString(@number, 4, 7)
	End
	Else if(len(@number) Between 11 AND 13)
	Begin
		--format like: ??9-999-999-9999
		Select	@country = subString(@number, 1, len(@number) - 10)
			,@area = left(right(@number, 10),3)
			,@local = right(@number, 7)
	End
	Else if(len(@number) = 14)
	Begin
		--format like: 999-999-9999-9999
		Select	@country = left(@number, 3)
			,@area = left(right(@number, 11),3)
			,@local = right(@number, 8)
	End
	Else if(len(@number) >= 15)
	Begin
		--format like: ???999-9999-9999-9999
		Select	@country = subString(@number, 1, len(@number) - 12)
			,@area = left(right(@number, 12),4)
			,@local = right(@number, 8)
	End
	else if(len(@number) > 7)
	Begin
		SElect @local = right(@number, 7)
			,@area = left(@number, len(@number)-7)
	End
	else if (len(@number) > 0)
		Set @local = @number


	if (@part like 'c%' OR @part like 'i%') --country, international
		set @retVal = @country
	else if (@part like 'a%') --areacode
		set @retVal = @area
	else if (@part like 'l%') --local
		set @retVal = @local
	else if (@part like 'e%' OR @part like 'x%') --extension, x
		set @retVal = @ext
	else if (@part like 'f%')
	Begin

		if(len(@local) > 4)
			set @local = left(@local, len(@local)-4) + '-' + right(@local, 4)
		
		set @retVal = isNull(@country + '-','') 
			+ isNull(@area + '-', '')
			+ isNull(@local,'')
			+ isNull(' ext: ' + @ext, '')
	End
	else if (@part like 's%')
	Begin
		set @retVal = isnull(@country,'')
			+ isNull(@area,'')
			+ isNull(@local,'')
	End


	return nullif(@retVal,'')

End
GO

Go
Create Function dbo.formatPhone(@phone varchar(30))
Returns varchar(30) As
Begin
	Declare @rtnValue varchar(30)

	Set @Phone = dbo.getCharacters(@Phone,'0-9')
	Set @rtnValue = replace(case 
			when len(@phone) > 10 
				then stuff(stuff(stuff(stuff(@phone,11,0,' x'),7,0,'-'),4,0,') '),1,0,'(')
			when len(@phone) = 10 
				then stuff(stuff(stuff(@phone,7,0,'-'),4,0,') '),1,0,'(')
			else @phone end,'(000) ','')

	Return @rtnValue
End
Go
Create Function dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
Returns varchar(500) AS
Begin
 
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

	Return @myString
End
Go

Declare @phoneNumbers table (phone varchar(25))

Insert Into @PhoneNumbers Select '(785) 555-3100'
Insert Into @PhoneNumbers Select '(785)555-3100'
Insert Into @PhoneNumbers Select '785.555.3100'
Insert Into @PhoneNumbers Select '(785) 555-3100 ext. 1474'
Insert Into @PhoneNumbers Select '(785)555-3100 x 1474'
Insert Into @PhoneNumbers Select '785.555.3100 1474'
Insert Into @PhoneNumbers Select '785.555.3100.1474'

Insert Into @PhoneNumbers Select '(800) 948-9543'
Insert Into @PhoneNumbers Select '(800)646-5287'
Insert Into @PhoneNumbers Select '800-985-5698'
Insert Into @PhoneNumbers Select '800 763-6521x654'
Insert Into @PhoneNumbers Select '(800) 726-9871 x3654'
Insert Into @PhoneNumbers Select '8009489543'
Insert Into @PhoneNumbers Select '800 687 4906'
Insert Into @PhoneNumbers Select '800 354 6871 24569'
Insert Into @PhoneNumbers Select '(000) 948-9543'
Insert Into @PhoneNumbers Select '(000)646-5287'
Insert Into @PhoneNumbers Select '000-985-5698'
Insert Into @PhoneNumbers Select '000 763-6521x654'
Insert Into @PhoneNumbers Select '(000) 726-9871 x3654'
Insert Into @PhoneNumbers Select '0009489543'
Insert Into @PhoneNumbers Select '000 687 4906'
Insert Into @PhoneNumbers Select '000 354 6871 24569'


	Select 
		Phone,
		DisplayPhone = netprofit.dbo.formatPhone(Phone)
	From @PhoneNumbers

Go
Drop Function dbo.formatPhone
Drop Function dbo.getCharacters