这个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)