SQL query to find number of trailing zeros across columns

Question

I need an sql query that given table of values of the form

| id | day1 | day2 | day3 | day4 | day5 |
| 1  |  4   |  0   |  5   | 0    | 0    |
| 2  |  2   |  0   |  0   | 0    | 0    |

gives

| id | trailing_zeros |
| 1  | 2              |
| 2  | 4              |

that is, the number of consecutive trailing zeros in the days columns for each id (from day5 backwards)

Answer 1

I'd go for something like this. Of course this is assuming you only have 5 days:

SELECT
  id,
  CASE WHEN day5 = 0 THEN
    CASE WHEN day4 = 0 THEN
      CASE WHEN day3 = 0 THEN
        CASE WHEN day2 = 0 THEN
          CASE WHEN day1 = 0 THEN 5
          ELSE 4 END
        ELSE 3 END
      ELSE 2 END
    ELSE 1 END
  ELSE 0 END
  amount_of_zeros
FROM t

Awful, isn't it?

Answer 2

Here is possible solution

select id,
   length(@k:=concat(day1,day2,day3,day4,day5&&1)) 
    - length(trim(trailing '0' from @k)) as trailing_zeros 
from days_table