A big request to help with the following task, there are lines like: "0000 --- 111 --- 222000001234567890" "0012345678900001" "" that is, the lines consist of numbers from 0 to 9 and the character "-" It is necessary to count in each line the maximum number of consecutive characters 0 That is, in the first line the answer is: 5 in the second 4 ...

I only work with sql for a month .... I can't do it. thank you in advance!

  • I tried with replace, but nothing good happens - Anna Zakirova
  • Most likely you will not find a simple solution. I would suggest creating a custom function for solving the problem. - Akina
  • Yes, in one request you get something non-trivial and multi-storied, it’s not worth it. It would be easier to translate (), but it is not in MySQL. So decide, or not in MySQL, but on the client or write a function - Mike

1 answer 1

DELIMITER @@; CREATE FUNCTION CountZeroes (inString TEXT) RETURNS INT DETERMINISTIC NO SQL BEGIN DECLARE counter INT DEFAULT 0; DECLARE temp INT DEFAULT 0; SET inString = COALESCE(inString, ''); WHILE inString != '' DO IF LEFT(inString, 1) = '0' THEN SET temp = temp + 1; ELSE SET counter = GREATEST(counter, temp); SET temp = 0; END IF; SET inString = SUBSTRING(inString FROM 2); END WHILE; RETURN GREATEST(counter, temp); END; @@; DELIMITER ;

And then

 SELECT CountZeroes("0000---111---222000001234567890") , CountZeroes("0012345678900001");
  • thank! works! - Anna Zakirova