在SQL中把格式化浮点数

数据库把字段设计成了decimal，所以每⼀个存进去的字段都有五个有效数字~ 如：50000.00000

我之前的任务是把四个字段中的两个数字，两个条件，拼成 0select FactorName,RS.FactorID,

RS.RewardStandardID,RS.RuleTemplateID,

case ABS(ConditionLowerBound) when 999999999 then '' else convert(varchar, ConditionLowerBound) +case convert(varchar, ConditionLowerBoundContain) when 0 then '<' when 1 then '<=' endend +'X'+ case ABS(ConditionUpperBound) when 999999999 then '' else +

case convert(varchar, ConditionUpperBoundContain) when 0 then '<' when 1 then '<='end+convert(varchar, ConditionUpperBound) end AS Expression,StandardValue

from RewardStandard RS left join RewardComputingFactor RF on RS.FactorID = RF.FactorIDwhere 1=1

输出如下：

明显，那表达式很难看~所以，现在的任务要把它变成正常的数字：

⽹上搜了⼀下，发现没有现成的答案，可能⼤学都觉得很简单，⼜或者我表述得不清楚吧。⽐如本标题，我也不知应该如何表述得更好。呵呵~

试过如下的⽅法：

select right(1000+4555,3)

select stuff(convert(varchar,(1000+4655)),1,1,'')

select replicate('0',3-len(convert(varchar,5)))+convert(varchar,5)select right(1000+ConditionUpperBound,10) from dbo.RewardStandardselect * from RewardStandard

select to_char(12.1,'99.00') from RewardStandarddeclare @jj decimal(5,2)set @jj=123.00

select convert (varchar(30),@jj)

select CONVERT(varchar,convert(float,ConditionUpperBound)) from dbo.RewardStandard

发现有个⽅法取有个格式化后⼏位的好⽅法 如，5格式化为 005，select right(1000+5,3)，呵呵，是不是很简单呢？

⽽我的问题呢，到现在还没有解决~最后，我⽆意中组合了⼀下，找到了办法。就是先把数字先转换成float再转换成varchar，如下：

select CONVERT(varchar,convert(float,ConditionUpperBound)) from dbo.RewardStandard

所以，我的SQL就变成这个样⼦了~

select FactorName,RS.FactorID,

RS.RewardStandardID,RS.RuleTemplateID,BillingMonth,RS.BaseTableID,RS.FactorID,

case ABS(ConditionLowerBound) when 999999999 then '' else convert(varchar, convert(float,ConditionLowerBound)) +case convert(varchar, ConditionLowerBoundContain) when 0 then '<' when 1 then '<=' endend +'X'+ case ABS(ConditionUpperBound) when 999999999 then '' else +

case convert(varchar, convert(float,ConditionUpperBoundContain)) when 0 then '<' when 1 then '<='end+

convert(varchar, convert(float,ConditionUpperBound)) end AS Expression,convert(varchar, convert(float,StandardValue))from RewardStandard RS left join RewardComputingFactor RF on RS.FactorID = RF.FactorIDwhere 1=1