UDF_StringToTable 普通与递归实现及其简单拓展

最新推荐文章于 2022-06-24 16:11:35 发布

_克急克怒_

最新推荐文章于 2022-06-24 16:11:35 发布

阅读量699

点赞数 1

分类专栏： SQL Server 算法文章标签：递归 SQL 字符串转表打横打竖

本文链接：https://blog.csdn.net/yangjian900109/article/details/50999178

版权

SQL Server 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

算法

12 篇文章 0 订阅

订阅专栏

先是用while循环处理,用Table返回

USE RFIDEng
GO


/***********************************************************************
** 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)     
  RETURN
END

再看递归算法：

/***********************************************************************
** 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)     
  RETURN
END

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

在上面两个函数使用过程中发现两点问题：

1）会出现需要分解两个参数的情况，譬如第几个通道电池是多少，且通道数不一定连续。

2）MS SQL Server目前仅支持32层递归，限制了一定的使用范围

优化如下：

USE batMon
GO

/***********************************************************************
** CREATOR:JustinYang DateTime:2017-08-24
** FUNCTION:Change a String To Table
** USE:SELECT * FROM UDF_StringToTableNewEx('17D0201100001,1;17D0201100002,2;17D0201100003,3;17D0201100004,4;17D0201100005,5;17D0201100006,6;',',',';',1)
** Modified by:JustinYang 20171008 改用While循环
**************************************************************************/
ALTER FUNCTION UDF_StringToTableNewEx(
@sStr varchar(4000),
@SepaOp1 VARCHAR(5),
@SepaOp2 VARCHAR(5),
@i int = 1
)
RETURNS @StringTable TABLE(id int,sSubStr1 varchar(100),sSubStr2 varchar(100))
AS 
BEGIN
  DECLARE @CurrStr1 varchar(100)
  DECLARE @CurrStr2 varchar(100)
  SET @CurrStr1 = ''  
  IF @i < 1 RETURN
  /*由于SQL Server限制递归调用仅能执行32次此处采取递归方式会限制使用次数,改用While循环
  IF(len(@sStr)>LEN(@SepaOp1)+LEN(@SepaOp2))
  BEGIN  
		IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
		END   

		IF (SUBSTRING(@sStr,1,LEN(@SepaOp2)) = @SepaOp2 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp2))            
		END
    
		SET @CurrStr1 = CASE WHEN CHARINDEX(@SepaOp1,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp1,@sStr)-1) ELSE @sStr END
		SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr1,@sStr),LEN(@CurrStr1),'')

		IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
		END 
	 
		SET @CurrStr2 = CASE WHEN CHARINDEX(@SepaOp2,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp2,@sStr)-1) ELSE @sStr END    
		SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr2,@sStr),LEN(@CurrStr2),'')

		INSERT INTO @StringTable(id,sSubStr1,sSubStr2) VALUES (@i,@CurrStr1,@CurrStr2)
	
		SET @i = @i + 1
    
		INSERT INTO @StringTable ( id, sSubStr1,sSubStr2 )
		SELECT id,sSubStr1,sSubStr2 FROM UDF_StringToTableNewEx(@sStr,@SepaOp1,@SepaOp2,@i)

  END
  ELSE
  BEGIN
	   IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
	   BEGIN
		  SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
	   END 

	   IF (SUBSTRING(@sStr,1,LEN(@SepaOp2)) = @SepaOp2 )
	   BEGIN
		  SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp2))            
	   END
  
	   SET @CurrStr1 = CASE WHEN CHARINDEX(@SepaOp1,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp1,@sStr)-1) ELSE @sStr END
  
	   SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr1,@sStr),LEN(@CurrStr1),'')

	   IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
	   BEGIN
		  SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
	   END 
	 
	   SET @CurrStr2 = CASE WHEN CHARINDEX(@SepaOp2,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp2,@sStr)-1) ELSE @sStr END   
   
	   IF(LEN(@CurrStr1)>= 1)
		INSERT INTO @StringTable(id,sSubStr1,sSubStr2) VALUES (@i,@CurrStr1,@CurrStr2)   
  END */ 

  while((CHARINDEX(@SepaOp2,@sStr)>0) AND (CHARINDEX(@SepaOp1,@sStr)>0) )
  begin
     	IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
		END   

		IF (SUBSTRING(@sStr,1,LEN(@SepaOp2)) = @SepaOp2 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp2))            
		END
    
		SET @CurrStr1 = CASE WHEN CHARINDEX(@SepaOp1,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp1,@sStr)-1) ELSE @sStr END
		SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr1,@sStr),LEN(@CurrStr1),'')

		IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
		BEGIN
			SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
		END 
	 
		SET @CurrStr2 = CASE WHEN CHARINDEX(@SepaOp2,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp2,@sStr)-1) ELSE @sStr END    
		SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr2,@sStr),LEN(@CurrStr2),'')

		INSERT INTO @StringTable(id,sSubStr1,sSubStr2) VALUES (@i,@CurrStr1,@CurrStr2)
	
		SET @i = @i + 1
  end

  if len(@sStr)>0
  begin
  	IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
	BEGIN
		SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
	END 

	IF (SUBSTRING(@sStr,1,LEN(@SepaOp2)) = @SepaOp2 )
	BEGIN
		SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp2))            
	END
  end

  if (CHARINDEX(@SepaOp1,@sStr)>1)
  begin
	SET @CurrStr1 = CASE WHEN CHARINDEX(@SepaOp1,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp1,@sStr)-1) ELSE @sStr END
  
	SET @sStr = STUFF(@sStr,CHARINDEX(@CurrStr1,@sStr),LEN(@CurrStr1),'')

	IF (SUBSTRING(@sStr,1,LEN(@SepaOp1)) = @SepaOp1 )
	BEGIN
		SET @sStr = RIGHT(@sStr,LEN(@sStr)-LEN(@SepaOp1))            
	END 
	 
	SET @CurrStr2 = CASE WHEN CHARINDEX(@SepaOp2,@sStr)>0 THEN SUBSTRING(@sStr,1,CHARINDEX(@SepaOp2,@sStr)-1) ELSE @sStr END
	set @sStr = ''   
	INSERT INTO @StringTable(id,sSubStr1,sSubStr2) VALUES (@i,@CurrStr1,@CurrStr2)
  end
  ELSE if len(@sStr)>0 
  BEGIN
	   INSERT INTO @StringTable(id,sSubStr1,sSubStr2) VALUES (@i,@sStr,'')
	   set @sStr = ''
  end
  RETURN
END

测试语句

SELECT * FROM UDF_StringToTableNewEx('17D0201100001,1;17D0201100002,2;17D0201100003,3;17D0201100004,4;17D0201100005,5;17D0201100006,6;',',',';',1)

结果如下：

id	sSubStr1	sSubStr2
1	17D0201100001	1
2	17D0201100002	2
3	17D0201100003	3
4	17D0201100004	4
5	17D0201100005	5
6	17D0201100006	6

_克急克怒_

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
UDF_StringToTable 普通与递归实现及其简单拓展

今天闲来无事，写个函数备用。先是用while循环处理,用Table返回。 USE RFIDEngGO/************************************************************************* CREATOR:JustinYang DateTime:2016-03-28** FUNCTION:Change a Stri
复制链接

扫一扫

专栏目录