sqlserver中自定义字符串拆分函数

来源：互联网发布：成龙人品知乎编辑：程序博客网时间：2024/06/06 18:33

create FUNCTION [dbo].[Split]
(
@Content NVARCHAR(100),
@Seperator NVARCHAR(10)
)
RETURNS NVARCHAR(100)

AS
BEGIN
DECLARE @ci INT,--current index
@ni INT,--next index
@LenOfContent INT;
declare @re varchar(100);
DECLARE @temp VARCHAR(100);

SET @LenOfContent=DATALENGTH(@Content);
SET @ci=1;
SET @ni=CHARINDEX(@Seperator,@Content);--1 based ni=2
SET @re = '';
SET @temp = '';

        WHILE(@ni>0)
        BEGIN
  IF(@ni>=@ci+1)
  BEGIN
   IF @re = ''
   BEGIN
    SELECT @temp = industry FROM scrm_sys_industry WHERE id = SUBSTRING(@Content,@ci,@ni-@ci)
    SET @re = @temp;
   END
   ELSE
   BEGIN
    SELECT @temp = industry FROM scrm_sys_industry WHERE id = SUBSTRING(@Content,@ci,@ni-@ci)
    SET @re = @re + ',' + @temp;
   END

  END
  SET @ci=@ni+1;
  SET @ni=CHARINDEX(@Seperator,@Content,@ci);
END
IF(@ci<=@LenOfContent)
BEGIN
  SELECT @temp = industry FROM scrm_sys_industry WHERE id = SUBSTRING(@Content,@ci,@LenOfContent+1-@ci)
  set @re = @re + ',' + @temp;
END
RETURN @re;
END