There is a text field in the table, approximately of this content:

Какой-то текст ::request_points|0|request_schedule::90::/request_points|0|request_schedule:: Какой-то текст ::request_points|1|request_schedule::90::/request_points|1|request_schedule:: Какой-то текст . .

How to write a request that counts the sum of numbers enclosed between ::request_points|i|request_schedule:: . Those. in our case 90 + 90 = 180

Use REGEXP ?

 SELECT SUM(что?) REGEXP "(::request_count_plan::).*.*(::/request_count_plan::)"

Thanks to all!

  • 2
    regexp does not help as it can only check if the value matches the pattern. He does not know how to change the value. It seems not to do without stored procedure. - Mikhail Chibel
  • @MikhailChibel probably, I did not write clearly enough. It is necessary to calculate the sum of numbers, nothing needs to be changed. - romka.pm
  • I understood correctly. I said that a regular expression is likely to pull out all the numbers will not work. If you had only one number in the line then everything else went by. I would advise you to write a procedure for extracting the sum of numbers from a string - Mikhail Chibel

1 answer 1

Regular expressions in MySQL are not quite convenient, with their help it is almost impossible to solve the problem. It is best to create a stored function sum_request_schedule() , which will parse the string, extract numbers from it and summarize. For convenience, we introduce the additional function str_count() , which counts the number of occurrences of a substring in a string.

 DELIMITER // -- Вычисляет количество вхождений подстроки substr в строку content DROP FUNCTION IF EXISTS str_count// CREATE FUNCTION str_count( substr TEXT, content TEXT ) RETURNS INT READS SQL DATA BEGIN RETURN ROUND((LENGTH(content) - LENGTH(REPLACE(content, substr, ""))) / LENGTH(substr)); END//

Using this function, it is much more convenient to create the function we need sum_request_schedule()

 -- Подсчет суммы элементов в строке DROP FUNCTION IF EXISTS sum_request_schedule// CREATE FUNCTION sum_request_schedule( content TEXT ) RETURNS INT READS SQL DATA BEGIN DECLARE i INT DEFAULT 0; DECLARE pos INT DEFAULT 1; DECLARE start_num INT DEFAULT 0; DECLARE end_num INT DEFAULT 0; DECLARE result INT DEFAULT 0; WHILE i < str_count('request_schedule::', content) DO SET start_num := LOCATE('request_schedule::', content, pos); SET end_num := LOCATE('::', content, start_num + LENGTH('request_schedule::')); SET result := result + SUBSTRING( content, start_num + LENGTH('request_schedule::'), end_num - start_num - LENGTH('request_schedule::')); SET pos := end_num; SET i := i + 1; END WHILE; RETURN result; END//

After that, you can use the sum_request_schedule() function as a regular MySQL function.

 SELECT sum_request_schedule("Какой-то текст ::request_points|0|request_schedule::90::/request_points |0|request_schedule::Какой-то текст ::request_points|1|request_schedule::90::/request_points |1|request_schedule::Какой-то текст") AS result; +--------+ | result | +--------+ | 180 | +--------+