Q:

Max Consecutive occurrences in text string

I am trying to write a query to identify number of consecutive occurrences in text string SQL Server.
Sample Date     | Query Output
---------------------------
11001           | 2
10000           | 1
11111           | 5
10111           | 3
00000           | 0
10010           | 1
10001           | 1
00100           | 1
11001           | 2
10100           | 1
01110           | 3
00011           | 2
00000           | 0
10010           | 1
01111           | 4
00110           | 2
10011           | 2
10010           | 1
01101           | 2
01010           | 1
10101           | 1

A:

I think the simplest method is case and like:
select (case when col like '%11111%' then 5
             when col like '%1111%' then 4
             when col like '%111%' then 3
             when col like '%11%' then 2
             when col like '%1%' then 1
             else 0
         end)

There are -- no doubt -- more clever methods.  But I'd be surprised if there were a simpler method.

