I have the below function. Upon execution, it says 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version.' Please help me find the issue. MySQL - 5.7

CREATE FUNCTION count_Occurrence(str varchar(1000),x varchar(20)) RETURNS INT
BEGIN
SET @cn:=0;
WHILE (INSTR(str,x) <> 0) DO
    SET @cn=@cn 1;
    REPLACE(str,SUBSTRING(str,0,INSTR(str,x)),"");
END WHILE;
RETURN @cn;
END

Test input:  str= 'abc;sdifh;1231;hsjh;wque' x=';'
Output: 4

CodePudding user response：

How about an alternative implementation, like this one:

(CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, x, ''))) / CHAR_LENGTH(x)

To me at least, that implementation looks both simpler and more performant.

CodePudding user response：

If you prefer to stay closer to your original code, a couple of minor corrections are needed.

1. The SUBSTRING needs 1 as the second parameter, not 0.
2. The REPLACE returns the string after modifying, so need to add SET str = before the REPLACE to get the updated string as the loop processes.

Here's the code with those changes applied:

CREATE FUNCTION count_Occurrence(str varchar(1000),x varchar(20)) RETURNS INT
BEGIN
    SET  @cn = 0;
    WHILE (INSTR(str,x) <> 0) DO
        SET @cn = @cn   1;
        SET str = REPLACE(str,SUBSTRING(str,1,INSTR(str,x)),"");
    END WHILE;
    RETURN @cn;
END

Then running a test query:

SELECT count_Occurrence('abc;sdifh;1231;hsjh;wque', ';') AS found;

Gives:

See this example DB fiddle with the function and the query to check. In the first block after the version output shows that 0 with the SUBSTRING retrieves nothing.