Menu ▼



Posted By :
avatarmrkraju
Total Views : 419

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

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

Note
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 ]%'






comments powered by Disqus


Search dotnetlearners.com