FN_SPLIT-表值函数， 将字符串转列表

--  表值函数， 将字符串转列表。
-- 本函数不支持 “空格”作为分隔符，默认以逗号分隔。 
-- 2021/05/24
CREATE FUNCTION FT_SPLIT(
    @STRING NVARCHAR(4000),
    @separator NVARCHAR(10)
	)
	RETURNs @TABLE TABLE (ROW_ID INT, COLUMN_VALUE NVARCHAR(512))
	-- WITH ENCRYPTION
AS 
BEGIN 
  declare @return nvarchar(400);
  declare @tmpStr nvarchar(4000);
  declare @i int;
  declare @len int; 
  declare @rowid int;
  declare @value nvarchar(400);
  declare @pos int;
/*
例子：
SELECT * FROM DBO.FT_SPLIT('ABC,DECLARE,FGH,MONTH,Order',',')
返回
ROW_ID  COLUMN_VALUE
------  ------------
1	ABC
2	DECLARE
3	FGH
4	MONTH
5	Order
*/  
  
  -- 设置默认豆号(“,”)为分隔符
  if len(isnull(@separator,''))=0
    set @separator = ',';
	
  if @string =  @separator
  begin
     set @return = @string;
  end;
  -- 原字符串与分隔字符串并接 
  set @tmpstr = @string + @separator;

  set @i=0 ;
  set @len = 0;
  set @rowid = 0;
  set @pos =0;
  while @i <= len(@tmpstr)
  begin
     set @len = @len+1;
	 if substring(@tmpstr,@i,1) = @separator 
	 begin
	   set @rowid = @rowid +1 ;
	   set @value = substring(@tmpstr,@pos,@len-1);
	   insert into @table(ROW_ID, COLUMN_VALUE) 
	     VALUES (@ROWID , @value);
	   set @len = 0;	
	   set @pos = @i+1;  
	 end;
     
	 SET @i= @i+1;
  end;
  
 -- returns(@table);
  return 
END;