CREATE FUNCTION `getNum`(checkstr VARCHAR(200),separatorStr VARCHAR(10) ) RETURNS int(11)
begin
DECLARE returnVal int default 0;
DECLARE tmpstr VARCHAR(200) default '';
set checkstr = LCASE(LTRIM(rtrim(checkstr)));
set tmpstr = checkstr;
WHILE LENGTH(tmpstr) > 0 DO
select returnVal+1 into returnVal;
if locate(separatorStr,tmpstr) <= 0 then
return returnVal;
end if;
set tmpstr = SUBSTRING(tmpstr,LOCATE(separatorStr,tmpstr)+length(separatorStr)+1);
END WHILE;
return returnVal;
end;
谁能帮我改进下,谢谢
select *,(length(f1)-length(replace(f1,查找内容,'')))/length(查找内容)
from ttselect (length('1235')-length(replace('1235','35','')))/length('35')
begin
DECLARE returnVal int default 0;
select (length(checkstr)-length(replace(checkstr,separatorStr,'')))/length(separatorStr) into returnVal;
return returnVal;
end;
CREATE FUNCTION `getNum`(checkstr VARCHAR(200),separatorStr VARCHAR(10) ) RETURNS int(11)
begin
DECLARE returnVal int default 0;
select (length(checkstr)-length(replace(checkstr,separatorStr,'')))/length(separatorStr) into returnVal;
return returnVal;
end;
begin
DECLARE returnVal int default 0;
DECLARE tmpstr VARCHAR(200) default '';
set checkstr = LCASE(LTRIM(rtrim(checkstr)));
set tmpstr = checkstr;
WHILE LENGTH(tmpstr) > 0 DO
if locate(separatorStr,tmpstr) <= 0 then
return returnVal;
end if;
select returnVal+1 into returnVal;
set tmpstr = SUBSTRING(tmpstr,LOCATE(separatorStr,tmpstr)+length(separatorStr)+1);
END WHILE;
return returnVal;
end;哈哈
改成这样就ok了!