【数据库-MySql】函数 - 删除指定字符串

最新推荐文章于 2024-06-07 23:22:00 发布

少莫千华

最新推荐文章于 2024-06-07 23:22:00 发布

阅读量3k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/chenlu5201314/article/details/72851955

版权

数据库专栏收录该内容

63 篇文章 3 订阅

订阅专栏

方法一、

【增加】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) do
		set 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 ) do
		set 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