Split the string. get first value of split SQL Server 2005

Question

I have a table called TableReason with a column called Reason.

Reason datatype is varchar(100) and contains values like this 2,-2,22,33,0,2 for one row

I need to write an update statement for this table to accomplish: only need first value of split with comma, ie. 2 only needed.

 update TableReason
 set reason=--please help me on this.

Answer 1

...
set reason=CASE CHARINDEX(',', reason)
                         WHEN 0 THEN reason
                         WHEN 1 THEN ''
                         ELSE LEFT(reason, CHARINDEX(',', reason)-1)
           END

This deals with the 3 cases of

• no comma
• first character is a comma
• a number followed by a comma