How to strip all non-alphabetic characters from string in SQL Server?

Try this function:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))Returns VarChar(1000)ASBegin    Declare @KeepValues as varchar(50)    Set @KeepValues = '%[^a-z]%'    While PatIndex(@KeepValues, @Temp) > 0        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')    Return @TempEnd

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

Hope it helps.

Parameterized version of George Mastros' awesome answer:

CREATE FUNCTION [dbo].[fn_StripCharacters](    @String NVARCHAR(MAX),     @MatchExpression VARCHAR(255))RETURNS NVARCHAR(MAX)ASBEGIN    SET @MatchExpression =  '%['+@MatchExpression+']%'        WHILE PatIndex(@MatchExpression, @String) > 0        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')        RETURN @String    END

Alphabetic only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

Believe it or not, in my system this ugly function performs better than G Mastros elegant one.

CREATE FUNCTION dbo.RemoveSpecialChar (@s VARCHAR(256)) RETURNS VARCHAR(256) WITH SCHEMABINDING    BEGIN        IF @s IS NULL            RETURN NULL        DECLARE @s2 VARCHAR(256) = '',                @l INT = LEN(@s),                @p INT = 1        WHILE @p <= @l            BEGIN                DECLARE @c INT                SET @c = ASCII(SUBSTRING(@s, @p, 1))                IF @c BETWEEN 48 AND 57                   OR  @c BETWEEN 65 AND 90                   OR  @c BETWEEN 97 AND 122                    SET @s2 = @s2 + CHAR(@c)                SET @p = @p + 1            END        IF LEN(@s2) = 0            RETURN NULL        RETURN @s2