Update with partial string only 1

[cathyg18]

I'm a relative newbie. Can someone help me figure this out?

I have names stored in a table like this:

Name
Smith MD, John

Name is all one field.

I want to update that field without the MD, so it would be like this:

Name
Smith, John

Using Charindex, I can locate the starting position within each record where I want to begin removal of the MD, but I don't know how to say, 'Leave that out and begin again with John.'

In this example, I would want to include positions 1,2,3,4,5, leave out positions 6,7,8,9, and begin again with 10 through the end of the string.

How do I say this?

Thanks,

Cathy

 

[checkai]

update table
set name = replace(name,' MD,',',')
where name like '% MD,%'

this assumes that _MD, is valid to replace in all name fields...

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."