Function with CAST and SUBSTRING

Question

Function with CAST and SUBSTRING

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

This function would be to return the last two "955 12 " or to return the number without the last two digits " 955 12".

The value passed to function can vary by at least 3 characters 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 doing these tests

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

In the first example it was for returns 99 and in the second 01

mysql

asked by anonymous 22.07.2015 / 17:50

2 answers

How to list files in a directory? How do I create notifications?

score 3 · Answer 1

In order for the same function to return the last 2 digits or to return the number without the last 2 digits, you must not use the LENGTH within the function but instead on the call, follow the function and two examples of use. 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*/

score 1 · Answer 2

Resolved by making two changes. The INT value entry for BIGINT And put an 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 ;