select (case when charindex(id,'.')>0 then left(id,1)+'-'+convert(varchar(50),convert(int,right(id,3))) else id end) from mytable
select left(id,(charindex('.',id)-1))+'-'+cast(cast(right(id,len(id)-charindex('.',id)) as int) as varchar(100)) from tablename where charindex('.',id)>0
select (case when charindex(id,'.')>0 then left(id,charindex(id,'.')-1) +'-'+ltrim(rtrim(convert(varchar(50),convert(int,right(id,3))))) else id end) from MyTable
测试:create table mytable(id varchar(50)) insert into mytable values('1') insert into mytable values('2') insert into mytable values('1.001') insert into mytable values('1.002') insert into mytable values('2.011') insert into mytable values('2.012') insert into mytable values('2.121') select (case when charindex('.',id)>0 then (left(id,1)+'-'+convert(varchar(50),convert(int,right(id,3)))) else id end) from mytable 结果: 1 2 1-1 1-2 2-11 2-12 2-121(7 row(s) affected)
这一次对了,那么我上面说的那个问题怎么解决呢?是不是要用到update?
update tablename set id= left(id,(charindex('.',id)-1))+'-'+cast(cast(right(id,len(id)-charindex('.',id)) as int) as varchar(100)) where charindex('.',id)>0
这样更好:create table #mytable(id varchar(50)) insert into #mytable values('1') insert into #mytable values('2') insert into #mytable values('1.001') insert into #mytable values('1.002') insert into #mytable values('2.011') insert into #mytable values('2.012') insert into #mytable values('2.121') select case when charindex('.',id)>0 then left(id,charindex('.',id)-1)+'-'+convert(varchar(50),convert(int,substring(id,charindex('.',id)+1,8000))) else id end from #mytable
你这样就可以了:update mytable set id=case when charindex('.',id)>0 then left(id,charindex('.',id)-1)+'-'+convert(varchar(50),convert(int,substring(id,charindex('.',id)+1,8000))) else id end
select id, charindex('.',id) 小数点位置, (case when charindex('.',id)>0--如果有小数点 then (left(id,charindex('.',id)-1)+ '-'+ convert(varchar(50),convert(int,right(id,len(id)-charindex('.',id)))) ) else id end) from mytable
update mytable set id=(select (case when charindex('.',id)>0 then (left(id,1)+'-'+convert(varchar(50),convert(int,right(id,3)))) else id end))select * from mytable 结果:id -------------------------------------------------- 1 2 1-1 1-2 2-11 2-12 2-121(7 row(s) affected)
where charindex('.',id)>0
+'-'+ltrim(rtrim(convert(varchar(50),convert(int,right(id,3))))) else id end) from MyTable
insert into mytable values('1')
insert into mytable values('2')
insert into mytable values('1.001')
insert into mytable values('1.002')
insert into mytable values('2.011')
insert into mytable values('2.012')
insert into mytable values('2.121')
select (case when charindex('.',id)>0 then (left(id,1)+'-'+convert(varchar(50),convert(int,right(id,3)))) else id end)
from mytable
结果:
1
2
1-1
1-2
2-11
2-12
2-121(7 row(s) affected)
where charindex('.',id)>0
insert into #mytable values('1')
insert into #mytable values('2')
insert into #mytable values('1.001')
insert into #mytable values('1.002')
insert into #mytable values('2.011')
insert into #mytable values('2.012')
insert into #mytable values('2.121')
select case when charindex('.',id)>0 then left(id,charindex('.',id)-1)+'-'+convert(varchar(50),convert(int,substring(id,charindex('.',id)+1,8000))) else id end
from #mytable
charindex('.',id) 小数点位置,
(case
when charindex('.',id)>0--如果有小数点
then (left(id,charindex('.',id)-1)+
'-'+
convert(varchar(50),convert(int,right(id,len(id)-charindex('.',id))))
)
else id
end)
from mytable
结果:id
--------------------------------------------------
1
2
1-1
1-2
2-11
2-12
2-121(7 row(s) affected)