replacing some characters with space

Hi I have a condition where I have to replace the MiddleName  with space that has the following:
'and' 'jr' 'sr' '&*' 'of' anything containing numerics 'R1315', how do I change the below?

(CASE when ISNUMERIC(REPLACE(M.MiddleName, ',' , ' '))=1 THEN NULL ELSE MiddleName END)AS [Middle Name],
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For the number part you can use patindex to look for any digits:

case when patindex('%[0-9]%', m.MiddleName) > 0 then null else MiddleName end

Open in new window

I didn't quite understand the rest of the question though, what else do you need to do?
sqlcuriousAuthor Commented:
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?
PortletPaulEE Topic AdvisorCommented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

sqlcuriousAuthor Commented:
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!
PortletPaulEE Topic AdvisorCommented:
mmm, maybe it is just due to the structure of your case expression where you are doing the test using replace(replace.... but when it comes to the output itself you are not doing any replacements

  , 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 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(HNAI.dbo.Removenumbers(M.MiddleName),'and',''),'jr',''),'sr',''),'&*',''),'of',''),'^',''),'&',''),'*',''),' ','')
    END AS [Middle Name]
  ,   LastName
from table1

Open in new window

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?
sqlcuriousAuthor Commented:
Hi Snarf0001, I only have to replace where there is an 'of' by itself not just anywhere. for ex: there might be a last name: Rebekoff , I can't replace 'of' here by space.
Like you mentioned if there is a 'M of D' then I should be leaving it as it is.
I won't be changing the source table, just returning the results.
I think what Paul Maxwell should work, will check it out.
thanks all!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlcuriousAuthor Commented:
thanks for your inputs
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.