我有String asdasdwdfef,rgrgtggt,weef,我希望输出像表格一样,如下图所示
@H_502_5@id decription
1 asdasdwdfef
2 rgrgtggt
3 weef
为此,我创建了一个程序
这是我的程序
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
但是当我使用以下命令执行它时
@H_502_5@call Split('asdasdwdfef,weef',',');
它给了我以下输出：
@H_502_5@id decription
1 NULL
2 NULL
3 NULL
最佳答案
我得到了答案
首先创建新功能
@H_502_5@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,pos -1)) + 1),'');
然后创建存储过程
@H_502_5@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,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
之后我通过调用Split调用它(‘asas,d,sddf,dfd’);它给了我想要的输出.
每个建议都是Thanx.