UDF_StringToTable 普通与递归实现

来源：互联网发布：json文件dw打开乱码编辑：程序博客网时间：2024/05/20 14:22

今天闲来无事，写个函数备用。先是用while循环处理,用Table返回

USE RFIDEngGO/************************************************************************* CREATOR:JustinYang DateTime:2016-03-28** FUNCTION:Change a String To Table** USE:SELECT * FROM UDF_StringToTable(',12,;36,;245,',',;')** SELECT * FROM UDF_StringToTable(',12,;36,;245,',',')**************************************************************************/ALTER FUNCTION UDF_StringToTable(@sStr varchar(4000),@SepaOp VARCHAR(5))RETURNS @StringTable TABLE(id int,sSubStr varchar(100))AS BEGIN  DECLARE @CurrStr varchar(100)  SET @CurrStr = ''    DECLARE @i int  SET @i = 1  WHILE(len(@sStr)>LEN(@SepaOp))  BEGIN    IF (SUBSTRING(@sStr,1,LEN(@SepaOp)) = @SepaOp )    BEGIN        SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp))                END    SET @CurrStr = CASE WHEN CHARINDEX(@SepaOp,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp,@sStr)-1) ELSE @sStr END     INSERT INTO @StringTable(id,sSubStr) VALUES (@i,@CurrStr)    SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr,@sStr),LEN(@CurrStr),'')    SET @i = @i +1  END  SET @CurrStr = CASE WHEN CHARINDEX(@SepaOp,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp,@sStr)-1) ELSE @sStr END   IF(LEN(@CurrStr)>= 1)   INSERT INTO @StringTable(id,sSubStr) VALUES (@i,@CurrStr)       RETURNEND

再看递归算法：

/************************************************************************* CREATOR:JustinYang DateTime:2016-03-28** FUNCTION:Change a String To Table** USE:SELECT * FROM UDF_StringToTableNew(',12,;36,;245,',',;',0)** SELECT * FROM UDF_StringToTableNew(',12,;36,;245,',',',1)**************************************************************************/CREATE FUNCTION UDF_StringToTableNew(@sStr varchar(4000),@SepaOp VARCHAR(5),@i int = 1)RETURNS @StringTable TABLE(id int,sSubStr varchar(100))AS BEGIN  DECLARE @CurrStr varchar(100)  SET @CurrStr = ''    IF @i< 1 RETURN  IF(len(@sStr)>LEN(@SepaOp))  BEGIN    IF (SUBSTRING(@sStr,1,LEN(@SepaOp)) = @SepaOp )    BEGIN        SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp))                END    SET @CurrStr = CASE WHEN CHARINDEX(@SepaOp,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp,@sStr)-1) ELSE @sStr END     INSERT INTO @StringTable(id,sSubStr) VALUES (@i,@CurrStr)    SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr,@sStr),LEN(@CurrStr),'')    SET @i = @i +1        INSERT INTO @StringTable ( id, sSubStr )    SELECT id,sSubStr FROM UDF_StringToTableNew(@sStr,@SepaOp,@i)  END  SET @CurrStr = CASE WHEN CHARINDEX(@SepaOp,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp,@sStr)-1) ELSE @sStr END   IF(LEN(@CurrStr)>= 1)   INSERT INTO @StringTable(id,sSubStr) VALUES (@i,@CurrStr)       RETURNEND

此处采用递归处理，批量插入返回的表变量。

1 0