Invalid length parameter passed to the RIGHT function in update statement

Question

does anybody know why my update is not working if I try to set two of the columns at the same time?

  UPDATE mytable
    SET [Customer] = RIGHT([Customer], CHARINDEX('#', REVERSE([Customer])) -1) ,
        [Segment] = RIGHT([Segment], CHARINDEX('#', REVERSE([Segment])) -1)
    WHERE CHARINDEX('#', [Customer]) > 0 OR
              CHARINDEX('#', [Segment]) > 0

If I run the query with updating just one column, then it works as it is expected to work. If I run with both, I get the following err:

Invalid length parameter passed to the RIGHT function. The statement has been terminated.

I know that this can happen when CHARINDEX returns 0 but I am trying to control that with the WHERE clause.

Answer 1

You have an OR in the WHERE clause. If you really want to control it, replace it with an AND:

UPDATE mytable
    SET [Customer] = RIGHT([Customer], CHARINDEX('#', REVERSE([Customer])) -1) ,
        [Segment] = RIGHT([Segment], CHARINDEX('#', REVERSE([Segment])) -1)
    WHERE CHARINDEX('#', [Customer]) > 0 AND
          CHARINDEX('#', [Segment]) > 0

Alternatively:

UPDATE mytable
    SET [Customer] = (CASE WHEN Customer LIKE '%#%'
                           THEN RIGHT([Customer], CHARINDEX('#', REVERSE([Customer])) -1)
                           ELSE Customer
                      END)
        [Segment] = (CASE WHEN Segment LIKE '%#%'
                          THEN RIGHT([Segment], CHARINDEX('#', REVERSE([Segment])) -1)
                          ELSE Segment
                     END)
    WHERE Customer LIKE '%#%' OR Segment LIKE '%#%';