Mysql拆分字符串并获取整数值

Question

I have trouble to split string and get only numeric value those are in round bracket. I try substring_index but can't get success.

Column

prd_code

HWC-4054 (100 Pcs available)
HWC-7514 (125 pcs available)
HWC-1516 (total 80 pcs available)
HWC-8008 (80pcs available)

Required output

prd_code

100
125

Thank you.

Answer 1

Please check below solutions using mysql function.

select prd_code,digits(SUBSTRING_INDEX( SUBSTRING_INDEX( prd_code , '(', -1 ), ')', 1)) as one from `your table`

use below mysql function :

DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
 DECLARE i, len SMALLINT DEFAULT 1;
 DECLARE ret CHAR(32) DEFAULT '';
 DECLARE c CHAR(1);

 IF str IS NULL
 THEN 
   RETURN "";
 END IF;

 SET len = CHAR_LENGTH( str );
 REPEAT
   BEGIN
     SET c = MID( str, i, 1 );
     IF c BETWEEN '0' AND '9' THEN 
       SET ret=CONCAT(ret,c);
     END IF;
     SET i = i + 1;
   END;
 UNTIL i > len END REPEAT;
 RETURN ret;
END |
DELIMITER ;

reference from this