Remove non alphabetic characters from string in sql

By using regularexpesssion and PatIndex we can remove the non alphabetic characters from a string in sql server. Below sql function will take the input string and remove the special characters and non alphabets and returns the output string.

SQL function to remove non alphabets

CREATE Function [dbo].[RemoveNonAlphabets](@input VarChar(4000)) Returns VarChar(4000) AS Begin Declare @Alphabets as varchar(50) Set @Alphabets = '%[^a-z]%' While PatIndex(@Alphabets, @input) > 0 Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '') Return @input End

SELECT dbo.RemoveNonAlphabets ('Hello 123 Welcome!')
Hello Welcome

If you dont want to remove the spaces then add a space in the regularexpesssion after z and before ] as below
Set @Alphabets = '%[^a-z ]%'