Friday 27 April 2018

Membuang Semua Karakter Numerik dari Data

Alhamdulillah, sudah ada yang memberikan solusinya di [1] :

 DELIMITER $$

 DROP FUNCTION IF EXISTS `numericOnly`$$

 CREATE FUNCTION `numericOnly`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

Referensi


  1. Remove all non-numeric characters from a field, https://stackoverflow.com/questions/31146942/remove-all-non-numeric-characters-from-a-field/31147842

No comments:

Post a Comment