Change data remove after the parens

Dwivedi Prigot Source

HI I have data in one column as this: in SQL Server 2008

Aetna (AETNA)

I need to update this so that it will be Aetna that is to remove everything after the first parens in an update mode.

sqlsql-server-2008

Answers

answered 3 months ago Aaron Dietz #1

You can use CHARINDEX() to find the position of the (, then select everything to the LEFT() of that:

SELECT RTRIM(LEFT('Aetna (AETNA)',CHARINDEX('(','Aetna (AETNA)')-1))

Need to subtract 1 from the length to also remove the (, and RTRIM() removes any extra blank space from the right side.

Just replace the hardcoded string with your column name.

The update would be:

UPDATE table
SET col = RTRIM(LEFT(col,CHARINDEX('(',col)-1))
WHERE col like '%(%'

answered 3 months ago Gordon Linoff #2

If you need to do this for all records:

update t
    set col = left(col, charindex(' (', col))
    where col like '% (%';

comments powered by Disqus