自定义函数问题

[yuzhifu1]

想用MYSQL做一个自定义函数，但老是报错，请帮忙看一下，不胜感激！

CREATE  function GetCharIndexNum (`@findstring` varchar(255),`@string` varchar(255))
returns int

BEGIN
   declare `@location` int ; 
   declare    `@num` int ;
    
   set @num =0;
   set @location = charindex (@findstring,@string);
   while @location >0  do
      begin
      set @num =@num +1;
      set @string =substring(@string,@location+1,len(@string));
      set @location = charindex (@findstring,@string);

    end
return @num 

END

[生命沉思者]

declare `@location` int ;
变量不要加@

还有应该把报错的内容也贴出来吧。

[ACMAIN_CHM]

楼主用的什么数据库，错误提示是什么？

[yuzhifu1]

引用 1 楼 kxjrzyk 的回复:

declare `@location` int ;
变量不要加@

还有应该把报错的内容也贴出来吧。

[SQL] CREATE function GetCharIndexNum (`@findstring` varchar(255),`@string` varchar(255))
returns int

BEGIN
declare `@location` int ;
declare `@num` int ;

set @num =0;
set @location = charindex (@findstring,@string);
while @location >0 do
begin
set @num =@num +1;
set @string =substring(@string,@location+1,len(@string));
set @location = charindex (@findstring,@string);

end
return @num

END

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'return @num

END' at line 17

[Rotel-刘志东]

retrun @num;加分号试一下

[生命沉思者]

已给你改好了，编译通过，你的不少语言用错了。

CREATE FUNCTION `GetCharIndexNum`(`findstring` VARCHAR(255),
                                  `string`     VARCHAR(255))
RETURNS INT
NO SQL
DETERMINISTIC
BEGIN
  DECLARE `location` INT;

  DECLARE `num` INT;

  SET num =0;

  SET location = charindex (findstring, string);

  WHILE location > 0 DO
    SET num = num +1;
    SET string = Substring(string, location+1, len(string));
    SET location = Charindex (findstring, string);
  END WHILE;

  RETURN num;
END;