CREATE DEFINER=` root ` @ ` localhost ` FUNCTION ` const_function_strings ` (STR varchar (50), s varchar (10)) RETURNS a varchar (50) CHARSET utf8 

The BEGIN 
DECLARE strSub varchar (50) default '; 
DECLARE startIndex int the default 1; 
DECLARE len int the default 1; 
While startIndex<=length (STR) do 


The set len=LOCATE (s, STR, startIndex) - startIndex; 
If the LOCATE (s, STR, startIndex)=0 then 
The set len=length (STR) - (startIndex - 1); 
The set startIndex=length (STR) + 1; 
end if; 
Set strSub=CONCAT (strSub, ", "
(select TypeName from types 
Where 
ID=the substring (STR, startIndex, len))); 

If the LOCATE (s, STR, startIndex) & gt; 0 then 
The set startIndex=LOCATE (s, STR, startIndex) + 1; 
end if; 


End the while; 
Set strSub=the substring (strSub, 2, length (strSub)); 
RETURN strSub; 
END 

An error is:

DECLARE strSub VARCHAR (50) DEFAULT ';
(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 'at line 7

CodePudding user response:

DELIMITER $$

The CREATE DEFINER=` root ` @ ` localhost ` FUNCTION ` const_function_strings ` (STR VARCHAR (50), s VARCHAR (10)) RETURNS a VARCHAR (50) CHARSET utf8

The BEGIN
DECLARE strSub VARCHAR (50) DEFAULT ';
DECLARE startIndex INT the DEFAULT 1;
DECLARE len INT the DEFAULT 1;
WHILE startIndex<=LENGTH (STR) DO


The SET len=LOCATE (s, STR, startIndex) - startIndex;
IF the LOCATE (s, STR, startIndex)=0 THEN
The SET len=LENGTH (STR) - (startIndex - 1);
The SET startIndex=LENGTH (STR) + 1;
END IF;
SET strSub=CONCAT (strSub, ", "
(SELECT TypeName FROM TYPES
WHERE
ID=the SUBSTRING (STR, startIndex, len)));

IF the LOCATE (s, STR, startIndex) & gt; 0 THEN
The SET startIndex=LOCATE (s, STR, startIndex) + 1;
END IF;


END the WHILE;
SET strSub=the SUBSTRING (strSub, 2, LENGTH (strSub));
RETURN strSub;
END $$

DELIMITER.

CodePudding user response:

reference 1st floor AHUA1001 response:

DELIMITER $$

The CREATE DEFINER=` root ` @ ` localhost ` FUNCTION ` const_function_strings ` (STR VARCHAR (50), s VARCHAR (10)) RETURNS a VARCHAR (50) CHARSET utf8

The BEGIN
DECLARE strSub VARCHAR (50) DEFAULT ';
DECLARE startIndex INT the DEFAULT 1;
DECLARE len INT the DEFAULT 1;
WHILE startIndex<=LENGTH (STR) DO


The SET len=LOCATE (s, STR, startIndex) - startIndex;
IF the LOCATE (s, STR, startIndex)=0 THEN
The SET len=LENGTH (STR) - (startIndex - 1);
The SET startIndex=LENGTH (STR) + 1;
END IF;
SET strSub=CONCAT (strSub, ", "
(SELECT TypeName FROM TYPES
WHERE
ID=the SUBSTRING (STR, startIndex, len)));

IF the LOCATE (s, STR, startIndex) & gt; 0 THEN
The SET startIndex=LOCATE (s, STR, startIndex) + 1;
END IF;


END the WHILE;
SET strSub=the SUBSTRING (strSub, 2, LENGTH (strSub));
RETURN strSub;
END $$

DELIMITER.

still won't do so

CodePudding user response:

 
Mysql> Delimiter//
Mysql> The CREATE DEFINER=` root ` @ ` localhost ` FUNCTION ` const_function_strings ` (STR varchar (50), s varchar (10)) RETURNS a varchar (50) CHARSET utf8 
-> The BEGIN 
-> DECLARE strSub varchar (50) default '; 
-> DECLARE startIndex int the default 1; 
-> DECLARE len int the default 1; 
-> While startIndex<=length (STR) do 
-> The set len=LOCATE (s, STR, startIndex) - startIndex; 
-> If the LOCATE (s, STR, startIndex)=0 then 
-> The set len=length (STR) - (startIndex - 1); 
-> The set startIndex=length (STR) + 1; 
-> end if; 
-> Set strSub=CONCAT (strSub, ", "
-> (select TypeName from types 
-> Where 
-> ID=the substring (STR, startIndex, len))); 
-> 
-> If the LOCATE (s, STR, startIndex) & gt; 0 then 
-> The set startIndex=LOCATE (s, STR, startIndex) + 1; 
-> end if; 
-> 
-> 
-> End the while; 
-> Set strSub=the substring (strSub, 2, length (strSub)); 
-> RETURN strSub; 
-> END//
Delimiter. Query OK, 0 rows affected, 1 warning (0.04 SEC) 

Mysql> Delimiter. 

CodePudding user response:

Where is the execution, database tools, or lines of code,