用逗号运算符分隔MySQL字符串 [英] MySQL String separation by comma operator
本文介绍了用逗号运算符分隔MySQL字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有字符串asdasdwdfef,rgrgtggt,weef
,我希望输出如下表所示的格式
I have String asdasdwdfef,rgrgtggt,weef
and i want output like in table format as shown below
id decription
1 asdasdwdfef
2 rgrgtggt
3 weef
为此,我创建了一个过程 这是我的程序
For this i created a procedure here is my procedure
DELIMITER ;;
CREATE Procedure Split(_RowData text, _Delimeter text)
BEGIN
DECLARE _Iterator INT default 1;
DECLARE _FoundIndex INT;
DECLARE _Data varchar(255);
SET _FoundIndex = LOCATE(_Delimeter,_RowData);
DROP TABLE IF EXISTS _RtnValue;
CREATE temporary TABLE _RtnValue(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID));
WHILE _FoundIndex > 1 DO
INSERT INTO _RtnValue (description)
SELECT
_Data = LTRIM(RTRIM(SUBSTRING(_RowData, 1, _FoundIndex - 1)));
set _RowData = SUBSTRING(_RowData, _FoundIndex + LENGTH(_Delimeter) / 2, LENGTH(_RowData));
SET _Iterator = _Iterator + 1;
SET _FoundIndex = LOCATE(_Delimeter, _RowData);
END WHILE;
INSERT INTO _RtnValue(description) SELECT _Data = LTRIM(RTRIM(_RowData));
select * from _RtnValue;
END
但是当我通过使用以下命令执行它
But when i execute it by using following command
call Split('asdasdwdfef,rgrgtggt,weef', ',');
它给了我以下输出:
id decription
1 NULL
2 NULL
3 NULL
请让我知道如何解决此问题. 我正在使用MySQL.
Please let me know how to fix this issue. I am using MySQL.
推荐答案
我得到了答案
首先创建新功能
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
然后创建存储过程
DELIMITER ;;
CREATE PROCEDURE Split(in fullstr varchar(255))
BEGIN
DECLARE a INT Default 0 ;
DECLARE str VARCHAR(255);
DROP TABLE IF EXISTS my_temp_table;
CREATE temporary TABLE my_temp_table(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID));
simple_loop: LOOP
SET a=a+1;
SET str=SPLIT_STR(fullstr,",",a);
IF str='' THEN
LEAVE simple_loop;
END IF;
#Do Inserts into temp table here with str going into the row
insert into my_temp_table (description) values (str);
END LOOP simple_loop;
select * from my_temp_table;
END
之后,当我通过call Split('asas,d,sddf,dfd');
调用它时,它将为我提供所需的输出.
After that when i call it by call Split('asas,d,sddf,dfd');
it gives me the output that what i want.
感谢每一个建议.
这篇关于用逗号运算符分隔MySQL字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文