T-SQL 实现 Split 的方法

 DECLARE @list VARCHAR(100), @cnt INT, @idxStart INT, @idxEnd INT
 DECLARE @sites TABLE(id INT)
  
  SET @list = '23, 34, 44, 56, 78';
  --SET @list = '23';
  
  SET @list = replace(@list, ' ', '')
  
  IF(RIGHT(@list, 1)<>',')
       SET @list = @list + ','
 
 SET @cnt = len(replace(@list, ',', ',,'))-len(@list);
 SET @idxStart = 0
 SET @idxEnd = 0
 
 WHILE @cnt > 0
 BEGIN
       SET @idxEnd = charindex(',', @list, @idxStart)
       INSERT INTO @sites VALUES(convert(INT, substring(@list, @idxStart, @idxEnd-@idxStart)));
     
       SET @idxStart = @idxEnd + 1
       SET @cnt = @cnt - 1
 END
 
SELECT * FROM @sites;

declare @sites table(id varchar(10))
declare @str1 varchar(50)
declare @i int
set @str1=',,12,23 , 54,'
set @str1=replace(@str1,' ','')
set @str1= replace(ltrim(rtrim(replace(@str1,',',' '))),' ',',')
set @i=charindex(',',@str1)
while @i>=1
begin
    insert into @sites values(left(@str1,@i-1))
    set @str1=substring(@str1,@i+1,len(@str1)-@i)
    set @i=charindex(',',@str1)
end
if @str1<>''
    insert into @sites values(@str1)
select * from @sites

SET @cnt = len(replace(@list, ',', ',,'))-len(@list);