Remove Non-Alphabet Characters from a String–SQL Server

Suppose you have a string that contains numbers and other special characters and you want to keep only alphabets and remove all the other characters. One way to do this is to use a while loop that parses each character

Here is the code for the same

declare @str varchar(20)
set @str='ab12#89L(h12k'
Select @str as 'Original String'
declare @temp_str varchar(20), @i int
select @temp_str ='',@i=1
while @i<=len(@str)
begin
    set @temp_str=@temp_str+case when substring(@str,@i,1) like '[a-zA-Z]' then substring(@str,@i,1) else '' end
    set @i=@i+1
end
select @temp_str as 'String with Alphabets'

The code inside the While loop takes each character and check if it is in the range a-z or A-Z and appends that character with another variable. Other characters will not get appended. So at the end of while loop, the second variable will have only alphabets.