Condition text field character insert with SQL Update statement

Question

I need to update a field in a SQL-Server table conditionally. The field contains single-letter indicators of various status types, all together in the field. e.g. "I" for Insured, "O" for Other Insured, "H" for Health Qualified, etc., but together as a composite. The field might contain nothing, any of the letters, or all of the letters. e.g. "IHO" or "HOI" or "O" or "OI" etc. Any given single letter should never appear more than once in the field, though.

The application is running through a set of records based on unique ID, and updating just this field given the record's unique ID. Is it possible to, in a single SQL Update statement, accomplish the equivalent of:

"If this field already has an 'I' in it, don't do anything; otherwise, add an 'I' to the existing set of letters."

...and, if so (of course), what would that Update statement look like?

I am far from a SQL expert (obviously), so please be gentle. :)

Answer 1

Something like

UPDATE MyTable
SET StatusTypes= StatusTypes+ 'I'
WHERE UniqueID=5
AND CHARINDEX('I', StatusTypes)=0