DECLARE @S VARCHAR(1000) SET @S='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'DECLARE @SQL VARCHAR(8000) SET @SQL = REPLACE(@S,',',''' UNION ALL SELECT ''') EXEC('SELECT ''' + @SQL +'''')
如果保证都是数字,DECLARE @S VARCHAR(1000) SET @S='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'DECLARE @SQL VARCHAR(8000) SET @SQL = REPLACE(@S,',',' UNION ALL SELECT ') EXEC('SELECT ' + @SQL)
declare @char varchar(1000) set @char='1,10,11,12,13,14,15,16,17,18,19'while (charindex(',',@char)>0) begin insert into test values(substring(@char,0,charindex(',',@char))) -- select substring(@char,0,charindex(',',@char)) set @char=substring(@char,charindex(',',@char)+1,len(@char)+1) end insert into test values(@char)
/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr)while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) endif @inputstr <> '\' insert @temp values(@inputstr)return end go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr)while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) endif @inputstr <> '\' insert @temp values(@inputstr)return end go--调用declare @s varchar(1000)set @s='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'select * from dbo.fn_split(@s,',')drop function dbo.fn_split /* a ------- 1 10 11 12 13 14 15 16 17 18 19 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43(所影响的行数为 34 行) */
SET @S='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'DECLARE @SQL VARCHAR(8000)
SET @SQL = REPLACE(@S,',',''' UNION ALL SELECT ''')
EXEC('SELECT ''' + @SQL +'''')
SET @S='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'DECLARE @SQL VARCHAR(8000)
SET @SQL = REPLACE(@S,',',' UNION ALL SELECT ')
EXEC('SELECT ' + @SQL)
set @char='1,10,11,12,13,14,15,16,17,18,19'while (charindex(',',@char)>0)
begin
insert into test values(substring(@char,0,charindex(',',@char)))
-- select substring(@char,0,charindex(',',@char))
set @char=substring(@char,charindex(',',@char)+1,len(@char)+1)
end
insert into test values(@char)
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43'select * from dbo.fn_split(@s,',')drop function dbo.fn_split /*
a
-------
1
10
11
12
13
14
15
16
17
18
19
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43(所影响的行数为 34 行)
*/