--示例数据
declare @t table(D1 varchar(30))
insert @t select '1.023'
union all select '1.0000'
union all select '4.220'
union all select '大于1.00'
union all select '小于5.00'
union all select '小于7'
--查询处理
select D1=case
when charindex('.',D1)=0 or right(D1,1)<>'0'
then D1
when substring(D1,patindex('%[^0]%',reverse(D1)),1)='.'
then left(D1,len(D1)-patindex('%[^0]%',reverse(D1))+1)
else left(D1,len(D1)-patindex('%[^0]%',reverse(D1)))
end
from @t