mySQL存储过程,用于通过定界符分割字符串 [英] mySQL Stored Procedure for splitting strings by delimiter
问题描述
我正在编写一个存储过程,该存储过程会爆炸一个传递的字符串 传递了定界符并返回结果的第n个元素. n通过 也是.
I'm into writing a stored procedure which explodes a passed string by a passed delimiter and returns the n-th element of the result. n is passed too.
这就是我想出的:
CREATE PROCEDURE SPLIT(IN strToSplit text, IN strDelimiter varchar(1), IN nPartToGet int,OUT strSlice varchar(255))
BEGIN
SET strSlice = replace(substring(substring_index(strToSplit, strDelimiter, nPartToGet),
length(substring_index(strToSplit,strDelimiter, nPartToGet - 1)) + 1), strDelimiter, '')
END
;
可悲的是,mysql一直在困扰我我那里有语法错误.恕我直言,这应该工作.有人可以戳我哪里出问题了吗?
Sadly mysql keeps naging me that I've got an syntax error in there. IMHO this should work. Could anyone pls poke me on where I'm going wrong?
预先感谢
K
推荐答案
您需要在SET后面加上';'并且,在客户解释的情况下;作为定界符,您需要更改定界符,以便您可以输入实际值;进入程序.
You need to end your SET with a ';' and, given that the client interprets ; as the delimiter, you need to change the delimiter so you can enter an actual ; into the procedure.
mysql> delimiter //
mysql> CREATE PROCEDURE SPLIT(IN strToSplit text, IN strDelimiter varchar(1), IN nPartToGet int,OUT strSlice varchar(255))
-> BEGIN
-> SET strSlice = replace(substring(substring_index(strToSplit, strDelimiter,
-> nPartToGet), length(substring_index(strToSplit,strDelimiter,
-> nPartToGet - 1)) + 1), strDelimiter, '');
-> END
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> CALL SPLIT('1;2;3;4;5',';',3,@str);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @str;
+------+
| @str |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
相关文档: http://dev.mysql. com/doc/refman/5.0/en/stored-routines.html
这篇关于mySQL存储过程,用于通过定界符分割字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!