Here is the code to trim non-alpha charactersfrom the string. As you can see, this script uses tally table approach. Here is the use;
Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')
This will give the result 'Atif Sheikh', after trimming the no-alpha characters.