0

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

Any help for this MySQL novice would be greatly appreciated :)

Community
  • 1
  • 1
user3508325
  • 13
  • 2
  • 2
    Why do you want to do this in SQL? – Oliver Charlesworth May 24 '14 at 16:38
  • waiting for a good time I suppose? – Amit Joki May 24 '14 at 16:38
  • The string is a concatenation of a bunch of binary variables I've created in mysql, and I'd like the result of identifying the longest substring of unbroken ones to be used in further mysql queries. So long as it isn't too painful... – user3508325 May 24 '14 at 16:51
  • Too hard to do it since MySQL doesn't even have regexp_replace function. You better reconsider your approach to this task (i.e. write the data differently). But no doubt somebody will accept your question as a challenge and if you wait few hours or days, you might get a perfect answer. Welcome to Stack Overflow! – Hnatt May 24 '14 at 17:02
  • 1
    Without simple things like iterating through the contents it becomes less efficient to do this in sql than in whatever language you are calling the data from. You can store a separate column indicating a pre-determined number for that string to make things simple and easier. – Rogue May 24 '14 at 17:08

1 Answers1

0

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);
?>
Tanatos
  • 1,857
  • 1
  • 13
  • 12