select id,(case when charindex('*',comment)>0 then left(comment,charindex('*',comment)-1) else comment) comment from a
select id,(case when charindex('*',comment)>0 then left(comment,charindex('*',comment)-1) else comment end/*上边少个end*/) comment from a
select ID , substring(comment ,1,charindex('*',comment ) from A
select id,left(comment,len(comment)-charindex('*',comment)) from tb
declare @T table (ID int,comment varchar(6)) insert into @T select 1,'12*13' union all select 2,'124*3' union all select 3,'ASD*4R' union all select 4,'23D'select comment=left(comment,case when charindex('*',comment)-1=-1 then len(comment) else charindex('*',comment)-1 end ) from @T /* comment ------- 12 124 ASD 23D */
declare @T table (ID int,comment varchar(6)) insert into @T select 1,'12*13' union all select 2,'124*3' union all select 3,'ASD*4R' union all select 4,'23D'select left(comment+'*',charindex('*',comment+'*')-1) from @T /* 12 124 ASD 23D */
declare @T table (ID int,comment varchar(6)) insert into @T select 1,'12*13' union all select 2,'124*3' union all select 3,'ASD*4R' union all select 4,'23D'select id,left(comment,len(comment)-charindex('*',reverse(comment))) from @T/* id ----------- ------ 1 12 2 124 3 ASD 4 23D(4 行受影响) */
select id,(case when charindex('*',comment)>0 then left(comment,charindex('*',comment)-1) else comment) comment
from a
select id,(case when charindex('*',comment)>0
then left(comment,charindex('*',comment)-1)
else comment end/*上边少个end*/) comment
from a
declare @T table (ID int,comment varchar(6))
insert into @T
select 1,'12*13' union all
select 2,'124*3' union all
select 3,'ASD*4R' union all
select 4,'23D'select comment=left(comment,case when charindex('*',comment)-1=-1
then len(comment) else charindex('*',comment)-1 end ) from @T
/*
comment
-------
12
124
ASD
23D
*/
declare @T table (ID int,comment varchar(6))
insert into @T
select 1,'12*13' union all
select 2,'124*3' union all
select 3,'ASD*4R' union all
select 4,'23D'select left(comment+'*',charindex('*',comment+'*')-1) from @T
/*
12
124
ASD
23D
*/
insert into @T
select 1,'12*13' union all
select 2,'124*3' union all
select 3,'ASD*4R' union all
select 4,'23D'select id,left(comment,len(comment)-charindex('*',reverse(comment))) from @T/*
id
----------- ------
1 12
2 124
3 ASD
4 23D(4 行受影响)
*/