【数据库-MySql】函数

方法一、

【增加】DeleteChar【返回】（无）【类型】PROCEDURE【参数】IN `srcStr` varchar(20),IN `strDel` varchar(20),OUT `outStr` varchar(20)【定义】BEGIN#Routine body goes here...DECLARE pos int ;DECLARE length int ;DECLARE strtmp varchar(10);DECLARE strLeft varchar(10);DECLARE strRight varchar(10); set pos = INSTR(srcStr,strDel);set outStr = srcStr; while(pos > 0) doset strtmp = srcStr; set length = length(srcStr); set strLeft = LEFT(strtmp,pos-1);set strRight = RIGHT(strtmp,length - pos);set outStr = CONCAT(strLeft,strRight);set srcStr =  outStr;set pos = INSTR(srcStr,strDel);end while;END

调用

BEGIN   -- 删除 *号declare inTmp Varchar(20);declare outTmp Varchar(20);set inTmp = '*ceshi*meis';call DeleteChar(inTmp ,'*',@outTmp);set inTmp  = @outTmp;END

方法二、直接用Replace替换

BEGIN#Routine body goes here...DECLARE queryCount int ;DECLARE strBedNo varchar(10);DECLARE strUpdate varchar(10);SELECT count(*),bed_no into queryCount,strBedNo FROM patient WHERE bed_no LIKE '%*%' LIMIT 1;while(queryCount >=1 ) doset strUpdate = REPLACE(strBedNo,'*','');UPDATE patient set bed_no=strUpdate WHERE bed_no =  strBedNo;SELECT count(*),bed_no into queryCount,strBedNo FROM patient WHERE bed_no LIKE '%*%' LIMIT 1; end while;END