CODEWITHSUNDEEP
sqlmysqllongest-substring
user3508325
user3508325

Reputation: 13

MySQL -- find longest unbroken substring of ones within a string

In a MySQL database, I have a string '0001110011011110'

I'd like to return the number 4, to indicate the greatest number of consecutive ones in string.

Closest SO I could find was an answer to a similar question implemented in c Finding consecutive bit string of 1 or 0

Upvotes: 0

Views: 123

Answers (1)

Tanatos
Tanatos

Reputation: 1917

I'm not sure if you are interested in any kind of order in mysql, but if you are looking for a solution to just find the greatest number of consecutive ones in the string, here is a php solution you could use on every single row of your table (when showing data, or whenever) :

<?php
function max_occurences($str,$search)
{
  if (''===$str) return 0;
  preg_match_all('!(['.$search.'])\\1*!', $str, $substring);
  return max(array_map('strlen', $substring[0]));
}

$search = '1';
$string = '0001110011011110';
echo max_occurences($string,$search);
?>

Upvotes: 0

Related Questions