alter function CovertToStakeNO
(
@StakeNOColumnName real
)
RETURNS varchar(20)
AS
BEGIN
declare @tostring varchar (20);
declare @indexofpoint int;
set @tostring = (select cast(@StakeNOColumnName as varchar(20)) );
set @indexofpoint=charindex('.',@tostring );
return ('K'+substring(@tostring,0,@indexofpoint)+'+'+substring(@tostring ,@indexofpoint+1,5))
END
GOdeclare @a realset @a = '310.520'SELECT dbo.CovertToStakeNO(@a)
使用CHAR/VARCHAR要定义长度,不然默认长度为1,也就是为什么你得到1个字符的原因
substring(cast([收费起点桩号]as char),改为
substring(cast([收费起点桩号]as char(10)),长度应该要大于等于你字符的长度
如果是数字的话,会把无效的0去掉,例如0.53显示成.53,053 显示成53
小数会把末尾的0去掉,就象你上面说的那样