T-SQL 实现 Split

 1 DECLARE @list VARCHAR(100), @cnt INT, @idxStart INT, @idxEnd INT
 2 DECLARE @sites TABLE(id INT)
 3  
 4 SET @list = '23, 34, 44, 56, 78';
 5 --SET @list = '23';
 6  
 7 SET @list = replace(@list, ' ', '')
 8  
 9 IF(RIGHT(@list, 1)<>',')
10       SET @list = @list + ','
11  
12 SET @cnt = len(replace(@list, ',', ',,'))-len(@list);
13 SET @idxStart = 0
14 SET @idxEnd = 0
15  
16 WHILE @cnt > 0
17 BEGIN
18       SET @idxEnd = charindex(',', @list, @idxStart)
19       INSERT INTO @sites VALUES(convert(INT, substring(@list, @idxStart, @idxEnd-@idxStart)));
20      
21       SET @idxStart = @idxEnd + 1
22       SET @cnt = @cnt - 1
23 END
24  
25 SELECT * FROM @sites;

其实很简单, 应该也比较好理解. 网上包括园子里还有N多其它的实现, 可以看看这个(T-SQL的Split)感觉也不错的. 关键是想法.

转载于:https://www.cnblogs.com/Freeway/archive/2012/12/10/Split_via_T-SQL.html