I have a
name column which in some of the the cells contains a bracketed string.
Smith (Divorced) Jones Janes Renold (Deaceased)...
and so on. I need to remove the bracketed section completly.
Smith Jones Janes Renold
REPLACE but either get an invalid lenght error or it only removes part.
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