replacing some characters with space

Hi Snarf0001 thanks, this works for the number part. I just put what u mentioned in a function -HNAI.dbo.Removenumbers
for the rest this is what I Am doing:
(CASE when ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(HNAI.dbo.Removenumbers(M.MiddleName),'and',''),'jr',''),'sr',''),'&*',''),'of',''),'^',''),'&',''),'*',''),' ',''))=1 THEN NULL ELSE MiddleName END)AS [Middle Name],

but for some reason I still see 'of' in the results, any idea why it's not going away?

Please close your eyes for a moment.
At that point you will see exactly how much we know about your data :)

Please supply some sample data, and from that data, the result you want.

Id             First Name      Middle Name      Last Name
995959      Estate                            of               Steven      
850018      Dominic                           F                 Meiklejohn
850019      Yolanda                                           Cortese
850020      Ernest                          E                     Craumer
850022      Heidi                         W                     Hamilton
Sorry about that here is the sample data, let's just say I am unable to replace 'of' from the middle name(that's one of the conditions- replace 'of' with a space), let me know if you need more information.Thanks!

If it contains "of" anywhere, are you aiming to null the column itself?
Are you updating the source table or just trying to return the results?

And what if the middle name is "M of D" for example... are we talking about a contains anywhere?

thanks for your inputs