Adding a constant to each 3 numbers of a number string

Question

i tried my best to have a better title for the question but thats all i could make up. Well i will try to explain it by giving an example. Assume i have: Set x = 100200300; Set y = 10; B Now i want to add y into all first 3 numbers which is “100” then add it into “200” and so on. I was wondering if i can do it with “select left” function inside a loop? Not sure how to apply it tho therefore i am seeking for help here. Help me please. Bless

DELIMITER $$
CREATE PROCEDURE proc(

)
BEGIN

    DECLARE counter INT DEFAULT 1;
    set @x = '100200300400';
    set @y = 10;
    WHILE counter <= @x DO
        select concat(substring(@x,counter,3) + @y,substring(@x,(counter +3),3) + @y) x;
    END WHILE;

END$$

DELIMITER ;

Are the 'numbers' in x always 3 digits and always exactly hundreds ? is Y always 2 digits? is X always 3 'numbers'? what should your result look like for your example? — P.Salmon, May 28 '20 at 07:21

P.Salmon · Accepted Answer · 2020-05-28T09:27:37.333

0

Perhaps

SET @X = 100200300;
SET @Y = 10;

select concat(substring(@x,1,3) + @y,substring(@x,4,3) + @y,substring(@x,7,3) + @y) x;

+-----------+
| x         |
+-----------+
| 110210310 |
+-----------+
1 row in set (0.001 sec)

If x is of unknown length DROP PROCEDURE IF EXISTS P; DELIMITER $$ CREATE PROCEDURE p(

)
BEGIN

    DECLARE counter INT DEFAULT 1;
    set @x = '100200300400';
    set @y = 10;
    SET @OUT = '';
    L:WHILE counter <= @x / 3 DO
          #SELECT COUNTER;
          IF @x is null or COUNTER > 10 THEN LEAVE L; END IF;
          SET @OUT = CONCAT(@OUT,substring(@x,counter,3) + @y);
          SET @X = REPLACE(@X,@out,'');

        #select concat(substring(@x,counter,3) + @y,substring(@x,(counter +3),3) + @y) x;
        SET COUNTER = COUNTER + 3;

    END WHILE;
    select @out;
END $$

DELIMITER ;

CALL P();

DELIMITER ;

edited May 28 '20 at 09:27

answered May 28 '20 at 07:24

P.Salmon

17,104
2
12
19

Actually no , @y is always fixed but @x can be of any size , but it will be multiple of 3. where in the example i gave it is only (100200300) but it can be (100200300400) or (100200300400500) or (100200300400500600) . How can i takecare of that? – Rashid May 28 '20 at 07:34
Then as you suggested you need a loop and your code therefore has to be in a stored program. – P.Salmon May 28 '20 at 07:46
Yes thats what i thought, it is inside a procedure actually but i am not abled to make it work with the loop. Would you mind to help? – Rashid May 28 '20 at 07:53
Sure - publish what you have. – P.Salmon May 28 '20 at 08:07
Check it please. It does the calculation for 2 iterations only and it goes to an infinite loop. – Rashid May 28 '20 at 08:21
You aren't incrementing or testing counter in your loop so it will be infinite. Please see edit for suggested approach nb:- you probably should remove the counter limit for your real app. – P.Salmon May 28 '20 at 09:26
And you should read https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – P.Salmon May 28 '20 at 09:28
I tried to run it in my machine but it does not work. I do not get any feedback in the output box after i run it. I wonder why. – Rashid May 28 '20 at 10:00

Adding a constant to each 3 numbers of a number string

1 Answers1