SQL Azure – TSQL User Defined Function – Cleaning a Field of Specified Characters

Stack overflow sourced, adapted and personally tested code
How to strip all non-alphabetic characters from string in SQL Server

In an earlier post I was having issues as some email addresses I was pulling out from a field were captioned in <> using the following will remove those characters prior to identifying string patterns..

Remember this pulls them out and then compacts the resulting string this may or many not be what you are looking for. I have adapted from the Stack Overflow discussion to include characters I don’t want rid of.

CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp nvarchar(max))
Returns nvarchar(max)
AS
Begin

    Declare @KeepValues as nvarchar(50)
    Set @KeepValues = '%[^a-z0-9/@ £$+=?.\!]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

And running the example code we get..

SELECT dbo.RemoveNonAlphaCharacters('abcdefghijklmnopqrstuvwxyz1234567890 !"£$()_+=-{}[]:@~;\|<>?./') as txtCorrected

We get

WARNING Please note ^ % & and * ‘ ” – are reserved TSQL characters and including them in the function appears to break the logic that I wish to see in the pattern replacement.