求教一个更新表的解决方案

Question

被这问题困扰了很久，实在没办法了，求高手指教表A  
Id    Work1     work2   work3
---------------
1                      a,cs,dsd,ds
2  
3                      sd,ds
4                      sadWork3目前是由含","的字符串，我想把他们分开，变成
如果超过3个只取前3个表A  
Id    Work1     work2   work3
---------------
1       a         cs    dsd
2  
3       sd         ds   
4       sad求教了

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-11 10:19:43
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Id] int,[Work1] varchar(11),[work2] sql_variant,[work3] sql_variant)
insert [tb]
select 1,'a,cs,dsd,ds',null,null union all
select 2,null,null,null union all
select 3,'sd,ds',null,null union all
select 4,'sad',null,null
--------------开始查询--------------------------
SELECT id,
       work1 = PARSENAME(REPLACE(Work1,',','.'),1),   ----替换一下 '.' 因为 parsename 只认 '.' 
       work2 = PARSENAME(REPLACE(Work1,',','.'),2),
       work3 = PARSENAME(REPLACE(Work1,',','.'),3)
FROM 
       tb
----------------结果----------------------------
/* id          work1                                                                                                                            work2                                                                                                                            work3
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
1           ds                                                                                                                               dsd                                                                                                                              cs
2           NULL                                                                                                                             NULL                                                                                                                             NULL
3           ds                                                                                                                               sd                                                                                                                               NULL
4           sad                                                                                                                              NULL                                                                                                                             NULL(4 行受影响)
*/

Answer 2

我看了，用游标
fetch   next   from   cr_field  into @v_Work while   @@fetch_status=0
 begin
    select top 3 * from dbo.f_split,',')到这步不知道怎么处理了，我该怎么处理这里的结果集呢，因为有的没有3个，再次用游标会报数量不一致的错误

Answer 3

select top 3 * from dbo.f_split(@v_Work ',')  

Answer 4

create table tb(Id int, Work1 varchar(10), work2 varchar(10), work3 varchar(50))
insert into tb values(1 ,'','', 'a,cs,dsd,ds') 
insert into tb values(2 ,'','', '')
insert into tb values(3 ,'','', 'sd,ds') 
insert into tb values(4 ,'','', 'sad')
goselect id , 
       work1 = parsename(replace(work3,',','.'),4),
       work2 = parsename(replace(work3,',','.'),3),
       work3 = parsename(replace(work3,',','.'),2)
from tb where parsename(replace(work3,',','.'),4) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),3),
       work2 = parsename(replace(work3,',','.'),2),
       work3 = parsename(replace(work3,',','.'),1)
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),2),
       work2 = parsename(replace(work3,',','.'),1),
       work3 = ''
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is null and parsename(replace(work3,',','.'),2) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),1),
       work2 = '',
       work3 = ''
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is null and parsename(replace(work3,',','.'),2) is null and parsename(replace(work3,',','.'),1) is not null
union all
select id , 
       work1 = '',
       work2 = '',
       work3 = ''
from tb where work3 = ''
order by iddrop table tb 
/*
id          work1                                                                                                                            work2                                                                                                                            work3                                                                                                                            
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 
1           a                                                                                                                                cs                                                                                                                               dsd
2                                                                                                                                                                                                                                                                             
3           sd                                                                                                                               ds                                                                                                                               
4           sad                                                                                                                                                                                                                                                               （所影响的行数为 4 行）
*/

Answer 5

--> Title  : Generating test data [tb]
--> Author : wufeng4552
--> Date   : 2009-11-11 10:19:49
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (Id int,work3 nvarchar(16))
insert into [tb]
select 1,'a,cs,dsd,ds' union all
select 2,null union all
select 3,'sd,ds' union all
select 4,'sad'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000),      --包含多个数据项的字符串
@pos int,             --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
select ID,
       dbo.f_GetStr(work3,1,',')work1,
       dbo.f_GetStr(work3,2,',')work2,
       dbo.f_GetStr(work3,3,',')work3
from tb
/*
ID          work1                                                                                                work2                                                                                                work3
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           a                                                                                                    cs                                                                                                   dsd
2           NULL                                                                                                 NULL                                                                                                 NULL
3           sd                                                                                                   ds                                                                                                   
4           sad                                                                                                                                                                                                       (4 個資料列受到影響)*/

Answer 6

create table tb(Id int, Work1 varchar(10), work2 varchar(10), work3 varchar(50))
insert into tb values(1 ,'','', 'a,cs,dsd,ds') 
insert into tb values(2 ,'','', '')
insert into tb values(3 ,'','', 'sd,ds') 
insert into tb values(4 ,'','', 'sad')
go
update tb 
set work1 = n.work1,
    work2 = n.work2,
    work3 = n.work3
from tb m, 
(
select id , 
       work1 = parsename(replace(work3,',','.'),4),
       work2 = parsename(replace(work3,',','.'),3),
       work3 = parsename(replace(work3,',','.'),2)
from tb where parsename(replace(work3,',','.'),4) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),3),
       work2 = parsename(replace(work3,',','.'),2),
       work3 = parsename(replace(work3,',','.'),1)
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),2),
       work2 = parsename(replace(work3,',','.'),1),
       work3 = ''
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is null and parsename(replace(work3,',','.'),2) is not null
union all
select id , 
       work1 = parsename(replace(work3,',','.'),1),
       work2 = '',
       work3 = ''
from tb where parsename(replace(work3,',','.'),4) is null and parsename(replace(work3,',','.'),3) is null and parsename(replace(work3,',','.'),2) is null and parsename(replace(work3,',','.'),1) is not null
union all
select id , 
       work1 = '',
       work2 = '',
       work3 = ''
from tb where work3 = ''
)n
where m.id = n.idselect * from tbdrop table tb 
/*
Id          Work1      work2      work3                                              
----------- ---------- ---------- -------------------------------------------------- 
1           a          cs         dsd
2                                 
3           sd         ds         
4           sad                   （所影响的行数为 4 行）
*/

Answer 7

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Id] int,[Work1] varchar(11),[work2] sql_variant,[work3] sql_variant)
insert [tb]
select 1,'a,cs,dsd,ds',null,null union all
select 2,null,null,null union all
select 3,'sd,ds',null,null union all
select 4,'sad',null,null
-------------------------------select 
Work1=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),1)),
work2=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),2)),
work2=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),3))
from 
tb----------------------------word1   word2   word3
a cs dsd
NULL NULL NULL
sd ds NULL
sad NULL NULL

Answer 8

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Id] int,[Work1] varchar(11),[work2] sql_variant,[work3] sql_variant)
insert [tb]
select 1,'a,cs,dsd,ds',null,null union all
select 2,null,null,null union all
select 3,'sd,ds',null,null union all
select 4,'sad',null,nullGOwith cte
as
(
select 
Id,
Work1=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),1)),
work2=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),2)),
work3=REVERSE(PARSENAME(REVERSE(REPLACE(Work1,',','.')),3))
from 
tb

) update tb
set
Work1=a.Work1,
work2=a.work2,
work3=a.work3
from 
cte a

where 
tb.Id=a.Id

GO

select * from tb-----------Result-----------

Id          Work1      work2      work3                                              
----------- ---------- ---------- -------------------------------------------------- 
1           a          cs         dsd
2                                 
3           sd         ds         
4           sad                   （所影响的行数为 4 行）