Home >Database >Mysql Tutorial >MySQL字符串分割自定义函数_MySQL

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

WBOY
WBOYOriginal
2016-06-01 13:40:431367browse

bitsCN.com
/***方法一*/select * from dbo.split(’01__02__03’,’__’)   发生错误,返回的结果不是我们原本要的结果:       -------------------  --想要的结果   01   02   03  -----------------       --实际结果:   01   _02   _03   以前我也写过类似的字符串分割自定义函数,也没有想过上面出现的这样问题。   我原来的函数是这样的:   /*   StringToTable   */   CREATE FUNCTION StringToTable(@StringX varchar(8000),@Split nvarchar(10))   RETURNS @TableResult TABLE(TableID nvarchar(20))   AS   BEGIN   DECLARE @Index int 
   SET @Index=CHARINDEX(@Split,@StringX,1)   WHILE (@Index>=1)   BEGIN      INSERT INTO @TableResult SELECT LEFT(@StringX,@Index-1)   SELECT   @StringX=RIGHT(@StringX,LEN(@StringX)-@Index),@Index=CHARINDEX(@Split,@StringX,1)   END   IF(@StringX’’) INSERT INTO @TableResult SELECT @StringX   RETURN   END   使用类似的select * from dbo.split(’01__02__03’,’__’) 一样出问题。   经过一下的修改就可以了,修改后程序为:  /*   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; bitsCN.com

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn