折分字符串问题...

Question

参考
分拆列值原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 1

if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(10) 
) 
returns @temp table (a varchar(200)) 
as 
begin 
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
declare @s varchar(10)
declare @j  int
set @j=1
while @i >= 1 
begin 
set @s=left(@inputstr, @i - 1)
set @s='a'+ltrim(@j)+'='+@s
insert @temp values(@s) 
set @j=@j+1
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
insert @temp values('a'+ltrim(@j)+'='+@inputstr) 
return 
end
go 
--调用 
declare @sql varchar(1000) 
declare @s varchar(800)
set @sql='17,32,38,47' 
select @s=isnull(@s+',','')+a from dbo.fn_split(@sql,',') 
select @s
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
a1=17,a2=32,a3=38,a4=47*/

Answer 2

DECLARE @s VARCHAR(1000)
SET @s='a=''17,32,38,47'''
SELECT STUFF(s,1,1,'') FROM
(
SELECT x=CAST(REPLACE(REPLACE(REPLACE(@s,',','</r><r>'),'a=','<r>') + '</r>','''','') AS XML)
) a
CROSS APPLY
(
SELECT s=(
SELECT ',a' + RTRIM(ROW_NUMBER() OVER(ORDER BY GETDATE())) + '=' + x.value('.','VARCHAR(100)') 
FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),Type
).value('.','VARCHAR(1000)')
) b
/*
a1=17,a2=32,a3=38,a4=47
*/

Answer 3

create function func(@input varchar(30) )
return varchar(30) 
as 
begin
      declare @num int;
      declare @cnt int;
      declare @subStr  varchar(30);
      set  @cnt=0;
      set  @subStr   =    @input;      
      set     @num   =  charindex(',',subStr);
      while @num > 0 
      begin
         set @cnt = @cnt +1;
         print 'a' + rtrim(cast(@cnt as varchar(10))) + '=' +  substring(subStr,1,@num -1);
         set @subStr = right(@subStr,len(@subStr)-@num);
         set @num = charindex(',',subStr);
      end
           
end

Answer 4

create function func(@input varchar(30) )
return varchar(30) 
as 
begin
      declare @num int;
      declare @cnt int;
      declare @subStr  varchar(30);
      set  @cnt=0;
      set  @subStr   =    @input;      
      set     @num   =  charindex(',',subStr);
      while @num > 0 
      begin
         set @cnt = @cnt +1;
         print 'a' + rtrim(cast(@cnt as varchar(10))) + '=' +  substring(subStr,1,@num -1);
         set @subStr = right(@subStr,len(@subStr)-@num);
         set @num = charindex(',',subStr);
      end
           
end

Answer 5

其实我是想把它分成，四个为17,32,38,47的字符串，不是要等于"a1=17,a2=32,a3=38,a4=47"这个的结果．．．简单一点就可以了．．

Answer 6

if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(10) 
) 
returns @temp table (a varchar(200)) 
as 
begin 
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
declare @s varchar(10)
while @i >= 1 
begin 
set @s=left(@inputstr, @i - 1)
insert @temp values(@s) 
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
insert @temp values(@inputstr) 
return 
end
go 
--调用 
declare @sql varchar(1000) 
declare @s varchar(800)
set @sql='17,32,38,47' 
select @s=isnull(@s+',','')+a from dbo.fn_split(@sql,',') 
select @s
/*                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
17,32,38,47
*/

Answer 7

基本是不可实现。虽然可以事先定义好几个变量，然后用sp_executesql来执行，再将结果传入预先定义的变量，但事实上，你并不知道原来的字串里有多个",",即应该先定义多少个变量。如果变量定义在动态语句中，那只能在动态语句中操作，没有意义。你也可以先定义一定量的变量，但谁也不能保证这些变量够用。

Answer 8

if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(10) 
) 
returns @temp table (a varchar(200)) 
as 
begin 
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
declare @s varchar(10)
while @i >= 1 
begin 
set @s=left(@inputstr, @i - 1)
insert @temp values(@s) 
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
insert @temp values(@inputstr) 
return 
end
go 
--调用 
declare @sql varchar(1000) 
declare @s varchar(800)
set @sql='17,32,38,47' 
select @s=isnull(@s+',''','')+a+'''' from dbo.fn_split(@sql,',') 
select stuff(@s,1,0,'''')
/*                                                                                                                                                                                                         
                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
'17','32','38','47'
*/

Answer 9

DECLARE @s VARCHAR(1000)
SET @s='a=''17,32,38,47'''
SELECT @s=STUFF(s,1,1,'') FROM
    (
        SELECT x=CAST(REPLACE(REPLACE(REPLACE(@s,',','</r><r>'),'a=','<r>') + '</r>','''','') AS XML)
    ) a
CROSS APPLY
    (
        SELECT s=(
                    SELECT CHAR(10) + 'DECLARE @a' + RTRIM(ROW_NUMBER() OVER(ORDER BY GETDATE())) + ' INT' + CHAR(10) + 'SET @a' + RTRIM(ROW_NUMBER() OVER(ORDER BY GETDATE())) + '=' + x.value('.','VARCHAR(100)') 
                        FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),Type
                ).value('.','VARCHAR(1000)')
    ) b
PRINT @s/*
DECLARE @a1 INT
SET @a1=17
DECLARE @a2 INT
SET @a2=32
DECLARE @a3 INT
SET @a3=38
DECLARE @a4 INT
SET @a4=47
*/

Answer 10

 实在不好意思，可能我还没把问题说清楚．．请大家原谅．．
我在想把字符串　a="17,32,38,47"
折分成　a1="17"
       a2="32"
       a3="38"
       a4="47"谢谢各位．．

Answer 11

不是又回到上面了嗎
/*
a1=17,a2=32,a3=38,a4=47
*/

Answer 12

呃．．上面不是"a1=17,a2=32,a3=38,a4=47"吗？
我要这样的:　a1= "17 " 
           a2= "32 " 
           a3= "38 " 
          a4= "47 " 

Answer 13

if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(10) 
) 
returns @temp table (a varchar(200)) 
as 
begin 
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
declare @s varchar(10)
declare @j  int
set @j=1
while @i >= 1 
begin 
set @s=left(@inputstr, @i - 1)
set @s='a'+ltrim(@j)+'="'+@s+'"'
insert @temp values(@s) 
set @j=@j+1
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
insert @temp values('a'+ltrim(@j)+'="'+@inputstr+'"') 
return 
end
go 
--调用 
declare @sql varchar(1000) 
declare @s varchar(800)
set @sql='17,32,38,47' 
select @s=isnull(@s+' ','')+a+char(13) from dbo.fn_split(@sql,',') 
select @s
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
a1="17"
 a2="32"
 a3="38"
 a4="47"*/

Answer 14

噢，天啊，大哥，可能我还没说清楚..
能不能分别有四个字符串变分别等于17,32,38,47啊．？？而不是@s＝" a1= "17" a2="32"a3= "38"a4= "47" "
我很菜，实在不好意思．．．

Answer 15

select * from dbo.fn_split(@sql,',') 

Answer 16

晕喔，还不是我想要的结果．．太菜了．．没办法．请原谅．
我想要的是分别有四个变量：@a1="17"
     　　　　　　　　　　@a2="32"
    　　　　　　　　　　　@a3="38"
    　　　　　　　　　　　@a4="40"
谢谢wufeng4552 的支持．

Answer 17

declare @a varchar(100)
set @a='17,32,38,47'select id=identity(int,1,1),val=substring(@a,id,charindex(',',@a+',',id)-id) into #
from (select top 100 id=(select count(*) from sysobjects where id<a.id) from sysobjects a) b
where substring(','+@a,id,1)=','
--select * from #
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','select distinct ')+'[a'+rtrim(id)+']='+rtrim(val)
from #
exec(@sql+' from #')
/*
a1          a2          a3          a4
----------- ----------- ----------- -----------
17          32          38          47(1 行受影响)*/drop table #

Answer 18

你真要看这个效果,这样好了:
if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(1000) 
) 
returns varchar(1000) 
as 
begin 
declare @temp table(col varchar(1000))
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
declare @s varchar(1000)
declare @s1 varchar(1000)
declare @s2 varchar(1000)
set @s2 = ''
set @s1 = 'select '
declare @j  int
set @j=1
while @i >= 1 
begin 
set @s=left(@inputstr, @i - 1)
set @s='declare @a'+ltrim(@j)+' int ' + char(13) + 'set @a'+ltrim(@j)+'='''+@s+''''
set @s1 = @s1 + '@a' + ltrim(@j) + '+'
insert @temp values(@s) 
set @j=@j+1
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
begin
insert @temp values('declare @a'+ltrim(@j)+' int ' + char(13) + 'set ' + '@a'+ltrim(@j)+'='''+@inputstr+'''') 
set @s1 = @s1 + '@a'+ltrim(@j)
select @s2 = @s2 + col + char(13) 
from @temp
set @s2 = @s2 + char(13) +  @s1
end
return(@s2)
end
go 
--调用 
declare @sql varchar(1000) 
declare @s varchar(800)
set @sql='17,32,38,47' 
select @s = dbo.fn_split(@sql,',') 
print @s
exec(@s)/**
134
**/还是建议你不要去这样处理你的逻辑

Answer 19

把数据复制到Excel里，按逗号分列即可。需要再插入到表，则--insert into 表
 select * 
 FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',   
'Data Source= EXCEL及路径;
User ID=Admin;Password=;Extended Properties=Excel 8.0;')...sheet$

Answer 20

慢慢学习PS: 
我的目标是 ----> ^_^