Suppose you have a string that contains numbers and other special characters, and you want to keep only alphas, and remove everything else. One way to do this is to use a while loop that parses each character. The following SQL Server statement does just that.

--
-- Removes Non-Alpha Characters from String
--

declare @OriginalString varchar(20) = 'ab12#89L(h12k880c=AAa';
declare @StringWithOnlyAlpha varchar(20) = '';
declare @iterationCount int = 1;

while @iterationCount <= len(@OriginalString)
    begin
        set @StringWithOnlyAlpha = @StringWithOnlyAlpha +
            case
                when substring(@OriginalString, @iterationCount, 1) like '[a-zA-Z]'
                    then substring(@OriginalString, @iterationCount, 1)
                else ''
            end;
        set @iterationCount = @iterationCount + 1;
    end;

select @OriginalString as 'OriginalString';
select @StringWithOnlyAlpha as 'StringWithOnlyAlpha';