mysql自定义分割函数_MySQL字符串分割自定义函数

/*

StringToTable

*/

CREATE FUNCTION StringToTable(@StringX varchar(8000),@Split nvarchar(10))

RETURNS @TableResult TABLE(TableID nvarchar(20))

AS

BEGIN

DECLARE @Index int

DECLARE @LenIndex int

SELECT @LenIndex=LEN(@Split),@Index=CHARINDEX(@Split,@StringX,1)

WHILE (@Index>=1)

BEGIN

INSERT INTO @TableResult SELECT LEFT(@StringX,@Index-1)

SELECT

@StringX=RIGHT(@StringX,LEN(@StringX)-@Index-@LenIndex+1),@Index=CHARINDEX(@Split,@Stri

ngX,1)

END

IF(@StringX<>’’) INSERT INTO @TableResult SELECT @StringX

RETURN

END

/**

*方法二

*/

set @b='123;234;567;789';

CREATE TEMPORARY TABLE splittable(

id INT AUTO_INCREMENT primary key,

VALUE VARCHAR(20)

) ;

set @sql=concat(concat("insert into splittable(value) values ('",replace(@b,';',"'),('")),"')");

prepare stem from @sql;

execute stem;

select * from splittable;