UPDATE tblFoo
SET cFoo1 = 'ABC' + SUBSTRING(cFoo1,4,3)
WHERE LEFT(cFoo1,3) = 'ACB'

UPDATE tblFoo
SET cFoo1 = CASE LEFT(cFoo1,3) 
   WHEN 'ACB' THEN 'ABC' + SUBSTRING(cFoo1,4,3)
   WHEN 'DFE' THEN 'DEF' + SUBSTRING(cFoo1,4,3)
   END
WHERE LEFT(cFoo1,3) NOT IN ('ABC','DEF')

update table set column= replace(column,left(column,3),'')