Function with CAST and SUBSTRING

Question

This function would return the last two "95512" or to return the number without the last two digits "95512".

The value passed to function can vary, at least 3 character and at most 11

DELIMITER $$ 
DROP FUNCTION IF EXISTS subInteiro $$
CREATE FUNCTION subInteiro(nCOD INT, nIncio INT, nFim INT) RETURNS VARCHAR(500)
    DETERMINISTIC
BEGIN
    DECLARE lvl VARCHAR(500); 
 SET lvl = SUBSTRING(CAST(nCOD AS CHAR), nIncio, CHAR_LENGTH(CAST(nCOD AS CHAR)) - nFim ) ;
 RETURN (lvl);
END $$
DELIMITER ;

I’m taking these tests

SELECT subInteiro(4518486299,-2,2); # return 47
SELECT subInteiro(201,-2,2); # return 0

In the first example it was to return 99 and in the second 01

Answer 1

In order for the same function to return the last 2 digits or to return the number without the last 2 digits it is necessary that you do not use the LENGTH within the function but rather in its call, follow the function and two examples of use meeting your needs.

DELIMITER $$

DROP FUNCTION IF EXISTS `subInteiro` $$
CREATE FUNCTION `subInteiro` (nCOD VARCHAR(500), nIncio INT, nFim INT) RETURNS VARCHAR(500)
BEGIN
  DECLARE lvl VARCHAR(500);

  SELECT SUBSTRING(nCOD, nIncio, nFim) into lvl ;

  RETURN (lvl);
END $$

DELIMITER ;

SELECT subInteiro(4518486299, LENGTH(4518486299)-1, 2)  /*Retorna 99*/
SELECT subInteiro(4518486299, 1, LENGTH(4518486299)-2)  /*retorna 45184862*/

Answer 2

Solved by making two changes. The down payment changed from INT for BIGINT And put a IF to check if it is less than or equal to 3 characters.

DELIMITER $$ 
DROP FUNCTION IF EXISTS subInteiro $$
CREATE FUNCTION subInteiro(nCOD BIGINT, nIncio INT, nFim INT) RETURNS VARCHAR(500)
    DETERMINISTIC
BEGIN
    DECLARE lvl VARCHAR(500); 
    DECLARE ops INT;
    IF nIncio < 0 THEN
    SET ops=1;
    ELSE
        SET ops=2;
    END IF;
    SET lvl = SUBSTRING(CAST(nCOD AS CHAR), nIncio, CHAR_LENGTH(CAST(nCOD AS CHAR)) - IF(LENGTH(nCOD) <= 3, ops, nFim) );
  RETURN (lvl);
END $$
DELIMITER ;