/**
*  
*  This function takes a phone number string from Great Plains and extracts
*  the extension, if any.
*  
*  @param @inString The phone number character string that will be formated
*  @param @outString The extension string that will be returned
*/

create function dbo.ExtractGreatPlainsPhoneExtension
(
    @inString varchar( 21 )
)
returns dbo.PhoneExtension
as
begin

    declare @outString  dbo.PhoneExtension = ''
    declare @theLength int

    set @inString = ltrim( rtrim( @inString ) )
    set @theLength = len( @inString )
    if( isnull( @inString, '' ) != '' and @inString != '00000000000000' )
    begin
        set @outString = case
            when @theLength > 20 then substring( @inString, 11, 10 )
            when @theLength > 10 then substring( @inString, 11, @theLength - 10 )
            else ''
        end

        -- Strip off any leading zeros off the extension
        while( len( @outString ) > 0 and substring( @outString, 1, 1 ) = '0' )
        begin
            if( len( @outString ) = 1 )
            begin
                set @outString = ''
            end
            else
            begin
                set @outString = substring( @outString, 2, len( @outString ) - 1 )
            end
        end
    end
    return @outString

end
GO
GRANT EXECUTE ON  [dbo].[ExtractGreatPlainsPhoneExtension] TO [MssExec]
GO