deployed your function #3

create table #tmp (data float(50))

insert #tmp values(112.097)

select dbo.NewFormatFloat3(data,7) from #tmp

drop table #tmp

--112.0969999

So still altering digits.

Appreciate your zero-out code.

Jack

After change function to use float(50) I got

select dbo.NewFormatFloat2(152.463616,13)

--152.464

some cutoff

Jack

(1) I cannot change the column type. it is a vendor software and they should have used decimal(m,n)

(2) I change your function slightly, replacing decimal with Float. Still not fully...

Ray-SQL,

Thanks for you code. Much simpler and I will try to incorporate.

I used your function on a temp table ( float(50) or float)

create table #tmp (data float(50))

insert #tmp values(112.097)

select dbo.NewFormatFloat(data,12)...

As requested here is the code

CREATE FUNCTION [dbo].[FormatFloat](@dIn decimal(30,14),@p int)

RETURNS varchar(100)

AS

begin

if (@dIn is null) return null

declare @d decimal(30,14)

set @d=Round(@dIn,@p) --- Float precission is 15 digits

declare @intPart bigint

set...

Thanks for your opition.

Without converting to varchar(50), we can keep more than 7

declare @f decimal(38,20)

set @f=9999999.1234567890123456789012345678

select round(@f,20)

The key problem is about converting to varchar(50). I actually wrote a function...

Barry,

Thanks for you comments:

(1) I will try CTE, View and SQL 2008 Editor. Very good suggestions

(2) Re-engineering is not possible since it is a closed sysetm Crystall report. I can...

Jeff,

I do read all your guys comments and it is now hard to quote now:

(1) I am dealing with Crystal report in a closed sysetm so cannot have my...

Barry,

For big block...

Barry,

I just finished writing all 10 user TVF in T-SQL. Another developer wrote similar...

Jeff,

In that sense, we will end up with a...

Unless it has to do with effecient RegEx, I've not known that to be true. It would be interesting to try to solve your problem with a T-SQL only...

Jonathan,

Thanks for the suggestions for reproducing the bugs. I will try it in the next few weeks.

Yes, I am trying to convert to T-SQL. If I have choice I would...

This function is more or less like data mapping, not much...

Hi All,

Thanks for your response.

Jonathan,

I have not figured out.

Jeff,

The CLR Function, exec select statement, using datareader to iterate through, return result set in a table. The assembly...