MySQL自定义函数提取中文

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/FG24151110876/article/details/95961965

 函数构造

1、变量varstring

2、计算变量varstring的长度

3、截取varstring并正则判断

4、将判断结果追加到变量v_temp

5、循环3-4

6、返回v_temp

SET @c='/BayerPharmaAG//BayerPharmaAG拜耳医药保健有限公司';


set global log_bin_trust_function_creators=TRUE;
-- 定义函数
DROP FUNCTION GetChinse;
CREATE FUNCTION GetChinse(varstring VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_temp VARCHAR(255) DEFAULT '';

SET v_length=CHAR_LENGTH(varstring);
WHILE v_length>0 DO

	IF MID(varstring,v_length,1) REGEXP'[\\u4e00-\\u9fa5]' THEN
	SET v_temp=CONCAT(v_temp,MID(varstring,v_length,1));
-- ELSE
	-- statement_list
	END IF;

	SET v_length=v_length-1;
END WHILE;
RETURN REVERSE(v_temp);
END;

SELECT GetChinse(@c);

 

展开阅读全文

mysql自定义函数的问题 SOS

07-29

以前用sqlserver数据库 现在转到mysql上了 有几个函数不会转 其他都弄好了 ...请教一下高手 先谢谢了rnrnrn函数一 rnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornrnrnALTER FUNCTION [dbo].[fu_GetPhoneNamesByOfficeID]rn ( rn @OfficeID int rn ) rn RETURNS varchar(250) rnAS rnBEGINrnrn declare @Str varchar(250), @PhoneName varchar(50), @isFirst bitrn select @Str=''rn set @isFirst=0rnrn declare name_cur cursor scroll forrnrn select rn p.手机品牌+ ' ' + p.手机型号 rn from rn TB_BAS_Office_Model m inner join phoneInfo p on p.flngID=m.flngPhoneID and m.flngOfficeID=@OfficeIDrnrn order by p.手机品牌, p.flngID descrnrn open name_currnrn fetch name_currn into @PhoneNamernrn while @@fetch_status = 0rn begin rn if @isFirst=0rn beginrn set @isFirst=1rn select @Str=@Str + @PhoneNamern endrn elsern beginrn select @Str=@Str + ',' + @PhoneNamern endrn fetch next from name_currn into @PhoneNamern end rnrn close name_currn deallocate name_currn rn -- 返回值rn return @StrrnEndrnrnrn函数二rnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornrnrnALTER FUNCTION [dbo].[GetFilePathByPhoneID]rn( rn @PhoneID intrn) rn RETURNS varchar(200) rnAS rnBEGIN rn declare @filePath varchar(200)rn rn select @filePath=正面图片 from PhoneInfo where flngID=@PhoneIDrnrn -- 返回rn return @filePathrnEndrnrnrnrn函数三rnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornrnrnALTER FUNCTION [dbo].[GetMobileByPhoneID]rn( rn @PhoneID intrn) rn RETURNS varchar(200) rnAS rnBEGIN rn declare @Mobile varchar(200)rn rn select @Mobile=手机品牌 + ' ' + 手机型号 from PhoneInfo where flngID=@PhoneIDrnrn -- 返回rn return @MobilernEndrnrnrnrn这几个函数怎么转到mysql里啊 希望大家不吝赐教 会一个说一个 都会就更好了....再次感谢 论坛

从oracle到mysql:自定义函数

09-14

不知怎样在mysql(5.0)中定义函数,请教高手!rnrnrnoracle:rnrnCREATE OR REPLACE FUNCTION GETNAME(id IN NUMBER,tablename IN VARCHAR2) RETURN VARCHAR2rnASrn /*rn * NAME:rn * getProjectnamern *rn * PARAMETER:rn * id - 主键rn *rn *rn * RETURN:rn * out_name - 返回编号和名称rn *rn * AUTHOR:rn * 王新雯rn *rn *rn */rn out_name VARCHAR2(300);rnrn BEGINrnrn IF tablename = 'PROJECT' THENrn SELECT projectname INTO out_name FROM PROJECT WHERE PROJECTID= id; --项目rnrn END IF;rnrn IF tablename = 'WORK' THENrnrn SELECT WORKNAME INTO out_name FROM WORK WHERE WORKID =id ; --工程rnrn END IF;rn IF tablename = 'CHILDWORK' THENrnrn SELECT CHILDWORKNAME INTO out_name FROM CHILDWORK WHERE CHILDWORKID= id ;--单向工程rnrn END IF;rn IF tablename = 'ADDRESS' THENrnrn SELECT CALIAS INTO out_name FROM ADDRESS WHERE CONID=id ;--客商地址rnrn END IF;rn IF tablename = 'DEPARTMENT' THENrnrn SELECT DEPARTMENTNAME INTO out_name FROM DEPARTMENT WHERE DEPARTMENTID=id ;--部门rnrn END IF;rn IF tablename = 'VEHICLE' THENrnrn SELECT VEHICLEMARK INTO out_name FROM VEHICLE WHERE VEHICLEID= id ;--车辆rnrn END IF;rn IF tablename = 'SUBJECT' THENrnrn SELECT '('||SUBJECTCODE||')'||SUBJECTNAME INTO out_name FROM SUBJECT WHERE SUBJECTID= id ;--科目rnrn END IF;rn IF tablename = 'PERSONNEL' THENrnrn SELECT PERSONNELNAME INTO out_name FROM PERSONNEL WHERE PERSONNELID= id ;--人员rnrn END IF;rn IF tablename = 'BALANCEMODE' THENrnrn SELECT BALANCENAME INTO out_name FROM BALANCEMODE WHERE BALANCEID= id ;--结算方式rnrn END IF;rnrn IF out_name = NULL THENrn out_name := '';rn END IF;rnrnrn RETURN(out_name);rn END;rn/rn 论坛

没有更多推荐了,返回首页