MYSQL - Parse comma separated string of numbers and compare each to set number

Question

I have a requirement in MySQL to take a comma separated string of numbers ("2000, 2001, 2002, 2003, 2004") passed into a stored procedure and compare each one to a another number e.g. 2005. If any of the numbers are greater than or equal to my target 2005, I need to execute code, else leave the loop. I cannot figure out how to iterate over this comma delimited string, turn each into a number using CAST(), and compare it to my target number.

Generally comma-separated values and relational databases are not friends. Is there any way to break this up prior to calling the stored procedure? — tadman, Mar 21 '19 at 21:04
@tadman - unfortunately no, i'm only permitted to modify the stored procedure. the parameter that is passed into the stored procedure is a comma delimited string of numbers from an external system that I cannot modify. — REAL O G, Mar 21 '19 at 21:24

score 0 · Answer 1 · answered Mar 21 '19 at 21:39

Assuming the argument passed into the procedure is named arg_list ...

If the requirement is to determine if just some (any number other than zero) entries in the list is greater than 2005,

Then we can do something like this in the procedure:

a loop iterator and some work areas:

DECLARE i_          INT          DEFAULT 0;
DECLARE ls_number   VARCHAR(255) DEFAULT '';
DECLARE ls_greatest VARCHAR(255) DEFAULT '';

initialize, get first number in list, first number in list is greatest so far:

SET i_ := 1;
SET ls_number := TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(  arg_list  ,','),',',i),',',-1));
SET ls_greatest := ls_number;

WHILE ls_number > '' DO
   -- evaluate as numeric and compare
   IF ls_number+0 > ls_greatest+0 THEN 
      -- the one we just got is the biggest one so far, so keep it
      SET ls_greatest := ls_number;
   END IF;
   -- get next number in list
   SET i_ := i_ + 1;
   SET ls_number := TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(  arg_list  ,','),',',i),',',-1));
END WHILE;

-- ls_greatest now contains the largest number from the list  
IF ls_greatest+0  >= 2005 THEN 
   -- do some code 
END IF;

Note: this assumes that the list in arg_list is well formed, and doesn't contain spurious commas, e.g. if the list was '1,2,3,,4,5' the loop would exit after processing the 3

Harshana · Answer 2 · 2019-03-21T23:42:43.900

Defined the procedure as follows.

DELIMITER //

CREATE PROCEDURE getValue(txt varchar(100), abc varchar(100))
BEGIN 
    SET @i = 1;
    SET @txt = REPLACE(txt, '"', '');
    SET @last_element = SUBSTRING_INDEX(@txt, ",", -1);

    WHILE @txt != '' DO
        SET @element = SUBSTRING_INDEX(@txt, ",", @i);

        IF CAST(abc AS UNSIGNED) >= CAST(@element AS UNSIGNED)  THEN
            # execute your code
            SET @txt = '';
        ELSE
            IF !LOCATE(',',@txt) && @element = @last_element THEN
                # @element = @last_element comparison is not necessary
                SET @txt = '';
            ELSE
                SET @txt = REPLACE(@txt, CONCAT(@element,","), '');
            END IF;
        END IF;
    END WHILE;

END //

DELIMITER ;

Tried the following two procedure calls.

call getValue("200,400,100","100");
call getValue("200,400,600","100");

MYSQL - Parse comma separated string of numbers and compare each to set number

2 Answers2