一段取值的sql...(charindex的应用)

---问题---
/*
表test字段中field的值如下：
aa,cd;tt
tt
dd,pp;
qq;ttd

pp
tt,qqq;ttt;1
oo;tt

希望把字段值都更新一下，取第一个"，"号前的值，如果没有"，"号则取"；"前的值，如果值为空就还是为空，最终的值变成如下：
aa
tt
dd
qq

pp
tt
oo
*/

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'

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
from @test

 

----2----
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
*/ 

 

----2----
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
*/