Q:

SQL Server- pattern matching for numbers only and plus sign

I'm using the following function to sanitize a phone number field:
CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

How would I modify the pattern above so that it will taken into account the + sign being an acceptable character (for international numbers)?
Thankyou

A:

use '%[^0-9+]%' as the pattern.  This, however, will allow plus symbols anywhere in the number.  What you should really do is test if the first character is a '+', and if so, simply prefix it to the resulting sanitized number.  Here's my implementation:
CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    declare @index int
    declare @hasPlus bit

    set @strText = LTRIM(@strText)

    if( '+' = left(@strText, 1) )
    begin
        set @hasPlus = 1
    end
    else
    begin
        set @hasPlus = 0
    end

    set @index = PATINDEX('%[^0-9]%', @strText)

    WHILE @index > 0
    BEGIN
        SET @strText = STUFF(@strText, @index, 1, '')

        set @index = PATINDEX('%[^0-9]%', @strText)
    END

    if( 1 = @hasPlus )
    begin
        set @strText = '+' + @strText
    end

    RETURN @strText
END

