Remove string between () including () in SQL Server 2008

Question

I have a name column which in some of the the cells contains a bracketed string.

Examples:

Smith (Divorced)
Jones
Janes
Renold (Deaceased)...

and so on. I need to remove the bracketed section completly.

Smith
Jones
Janes
Renold

Tried various CHARINDEX's and REPLACE but either get an invalid lenght error or it only removes part.

Answer 1

Here's what you basically need, just modify it to suit your query:

declare @tmp table (name varchar(100))
insert @tmp values ('smith (divorced)' ) , ('jones'), ('renold (deceased)...')

select name
    , case 
        when charindex('(', name, 1) > 0 
            then rtrim(left(name, charindex('(', name, 1) - 1)) 
        else name 
      end as [name]
from @tmp

If you need to replace the data that you have, just issue an UPDATE, like below:

UPDATE Persons_Table
SET Name = case 
            when charindex('(', Name, 1) > 0 
                then rtrim(left(Name, charindex('(', Name, 1) - 1)) 
            else Name 
          end
WHERE charindex('(', Name, 1) > 0 -- could prove useful since you might not want to go
                                  -- over all of the data