这个sql更新语句该怎么写，多谢！
表test字段中field的值如下：
aa,cd;tt
tt
dd,pp;
qq;ttd
pp
tt,qqq;ttt;1
oo;tt
希望把字段值都更新一下，取第一个 "， "号前的值，如果没有 "， "号则取 "； "前的值，如果值为空就还是为空，最终的值变成如下：
aa
tt
dd
qq
pp
tt
oo
谢谢！
[解决办法]
create table Test(field varchar(100))
insert into Test
select 'aa,cd;tt ' union all
select 'tt ' union all
select 'dd,pp; ' union all
select 'qq;ttd ' union all
select ' ' union all
select 'pp ' union all
select 'tt,qqq;ttt;1 ' union all
select 'oo;tt '
select * from Test
select
case
when charindex( ', ',field)> 0 then left(field,charindex( ', ',field)-1)
when charindex( ', ',field)=0 and charindex( '; ',field)> 0 then left(field,charindex( '; ',field)-1)
else field
end
from Test
drop table Test
[解决办法]
create table test(field varchar(50))
insert test select 'aa,cd;tt '
union all select 'tt '
union all select 'dd,pp; '
union all select 'qq;ttd '
union all select ' '
union all select 'pp '
union all select 'tt,qqq;ttt;1 '
union all select 'oo;tt '
update test
set field=substring(field, 0, patindex( '%[,;]% ', field))
where patindex( '%[,;]% ', field)> 0
select * from test
--result
field
--------------------------------------------------
aa
tt
dd
qq
pp
tt
oo
(8 row(s) affected)
[解决办法]
--更新
declare @test table
(
field varchar(50)
)
insert into @test select 'aa,cd;tt '
insert into @test select 'tt '
insert into @test select 'dd,pp; '
insert into @test select 'qq;ttd '
insert into @test select ' '
insert into @test select 'pp '
insert into @test select 'tt,qqq;ttt;1 '
insert into @test select 'oo;tt '
update a set field = b.nfield
from
@test a ,
(
select case when charindex( ', ',field) > 0 then left(field,charindex( ', ',field) - 1)
when charindex( '; ',field) > 0 then left(field,charindex( '; ',field) - 1)
else field end as nfield,field
from @test
)b
where a.field = b.field
select * from @test
--结果
aa
tt
dd
qq
pp
tt
oo
[解决办法]
declare @s table(col varchar(20))
insert into @s select 'aa,cd;tt ' union all select
'tt ' union all select
'dd,pp; ' union all select
'qq;ttd ' union all select
' ' union all select
'pp ' union all select
'tt,qqq;ttt;1 ' union all select
'oo;tt '
select case when charindex( ', ',col + ', ' ) < charindex( '; ',col + ', ' ) then left(col,charindex( ', ',col + ', ' )-1)
else left(col,charindex( '; ',col + '; ' )-1) end
from @s
[解决办法]
declare @t table(str varchar(20))
insert into @t select 'aa,cd;tt '
insert into @t select 'tt '
insert into @t select 'dd,pp; '
insert into @t select 'qq;ttd '
insert into @t select ' '
insert into @t select 'pp '
insert into @t select 'tt,qqq;ttt;1 '
insert into @t select 'oo;tt '
select
left(str,case
when charindex( ', ',left(str,charindex( '; ',str)))> 0 then charindex( ', ',str)-1
when charindex( '; ',str)> 0 then charindex( '; ',str)-1
else len(str)
end) as str
from
@t
/*
str
--------------------
aa
tt
dd
qq
pp
tt
oo
*/
[解决办法]
declare @t table(str varchar(20))
insert into @t select 'aa,cd;tt '
insert into @t select 'tt '
insert into @t select 'dd,pp; '
insert into @t select 'qq;ttd '
insert into @t select ' '
insert into @t select 'pp '
insert into @t select 'tt,qqq;ttt;1 '
insert into @t select 'oo;tt '
update @t
set str=left(str,case
when charindex( ', ',left(str,charindex( '; ',str)))> 0 then charindex( ', ',str)-1
when charindex( '; ',str)> 0 then charindex( '; ',str)-1
else len(str)
end)
select * from @t
/*
str
------
aa
tt
dd
qq
pp
tt
oo
*/
[解决办法]
if object_id( 'pubs..test ') is not null
drop table test
go
create table test(field varchar(20))
insert into test(field) values( 'aa,cd;tt ')
insert into test(field) values( 'tt ')
insert into test(field) values( 'dd,pp; ')
insert into test(field) values( 'qq;ttd ')
insert into test(field) values( ' ')
insert into test(field) values( 'pp ')
insert into test(field) values( 'tt,qqq;ttt;1 ')
insert into test(field) values( 'oo;tt ')
select
case when charindex( ', ',field) > 0 then substring(field,1,charindex( ', ',field) - 1)
when charindex( ', ',field) <=0 and charindex( '; ',field) > 0 then substring(field,1,charindex( '; ',field) - 1)
when charindex( ', ',field) <=0 and charindex( '; ',field) <=0 then field
end as field
from test
drop table test
/*
field
--------------------
aa
tt
dd
qq
pp
tt
oo
（所影响的行数为 8 行）
*/
[解决办法]
create table test1 (field varchar(100))
insert into Test1
select 'aa,cd;tt ' union all
select 'tt ' union all
select 'dd,pp; ' union all
select 'qq;ttd ' union all
select ' ' union all
select 'pp ' union all
select 'tt,qqq;ttt;1 ' union all
select 'oo;tt '
declare @aa varchar(100),@bb varchar(100),@i as int
declare cur_aa cursor for
select field from test1
open cur_aa
fetch next from cur_aa into @aa
while @@FETCH_STATUS=0
begin
if @aa is null
begin
fetch next from cur_aa into @aa
continue
end
if charindex( ', ',@aa)> 0
set @i=charindex( ', ',@aa)
else
begin
if charindex( '; ',@aa)> 0
set @i=charindex( '; ',@aa)
end
set @bb=left(@aa,@i-1)
update test1 set field=@bb where current of cur_aa
fetch next from cur_aa into @aa
end
close cur_aa
deallocate cur_aa
select * from test1
-------结果---------------------
field
----------------------------------------------------------------
aa
tt
dd
qq
pp
tt
oo
（所影响的行数为 8 行）
[解决办法]
create table test (field varchar(20))
insert test select 'aa,cd;tt '
union all select 'tt '
union all select 'dd,pp; '
union all select 'qq;ttd '
union all select ' '
union all select 'pp '
union all select 'tt,qqq;ttt;1 '
union all select 'oo;tt '
update test set field=
case when charindex( ', ',field)> 0 then left(field,charindex( ', ',field)-1)
when charindex( ', ',field)=0 and charindex( '; ',field)> 0 then left(field,charindex( '; ',field)-1)
else field end
from test
to:天道 你的那种写法不是搂住的要求 因为如果;在前面 就会取;号前面的了
