April 17, 2008 at 2:33 am
I wanna select one column which its datatype is float.
how can I insert comma when it is more than thousand eg. 1,000 25,000
April 17, 2008 at 2:52 am
molecule_kaab (4/17/2008)
I wanna select one column which its datatype is float.how can I insert comma when it is more than thousand eg. 1,000 25,000
One could use the convert function, but adding commas is only supported for money and smallmoney data types. If your float fits into this (precision and scale), then you can use statements like:
select convert(varchar(100) , convert(money, 123456.1234), 1 )
If you need more decimal points, you would need to write your own function to add the commas.
Regards,
Andras
April 17, 2008 at 3:26 am
Ok, probably I missed something, but for a start it will do 🙂
CREATE FUNCTION myFormat ( @a1 FLOAT )
RETURNS VARCHAR(100)
AS BEGIN
DECLARE @result VARCHAR(100)
declare @sign char
IF @a1 < 0
SET @sign = '-'
ELSE
SET @sign = '+'
SET @a1 = ABS(@a1)
SET @result = CONVERT(VARCHAR(100), CONVERT(int, ( @a1 - FLOOR(@a1) )
* 1000), 2)
SET @result = @result + ','
+ CONVERT(VARCHAR(100), CONVERT(int, ( @a1 * 1000 - FLOOR(@a1 * 1000) )
* 1000), 2)
DECLARE @a int
SET @a = CONVERT(int, @a1)
SET @result = CONVERT(VARCHAR(100), CONVERT(int, @a) % 1000, 2) + '.'
+ @result
WHILE( @a >= 1000 )
BEGIN
DECLARE @x int
SET @result = CONVERT(VARCHAR(100), CONVERT(int, FLOOR(@a / 1000))
% 1000, 2) + ',' + @result
SET @a = @a - FLOOR(@a / 1000)
END
RETURN @sign + @result
END
Regards,
Andras
April 17, 2008 at 5:34 am
Thank you very much
It work very well. 😀
April 17, 2008 at 8:17 am
molecule_kaab (4/17/2008)
Thank you very muchIt work very well. 😀
Well, I knew I missed something, and I was not handling 0's properly after the decimal point. So an updated (but by no means perfect :)) version is:
drop function myFormat
go
CREATE FUNCTION myFormat ( @a1 FLOAT )
RETURNS VARCHAR(100)
AS BEGIN
DECLARE @result VARCHAR(100)
declare @sign char
IF @a1 < 0
SET @sign = '-'
ELSE
SET @sign = '+'
SET @a1 = ABS(@a1)
SET @result = CONVERT(VARCHAR(100), CONVERT(int, ROUND(( @a1 - FLOOR(@a1))*1000,1)))
SET @result = REPLICATE('0', 3-datalength(@result)) + @result
DECLARE @p2 varchar(10)
SET @p2 = CONVERT(VARCHAR(100), CONVERT(INT, ROUND(( @a1 * 1000 - FLOOR(@a1 * 1000.0) ) * 1000, 1), 2))
SET @p2 = REPLICATE('0', 3 - DATALENGTH(@p2)) + @p2
SET @result = @result + ',' + @p2
DECLARE @a int
SET @a = CONVERT(int, @a1)
SET @result = CONVERT(VARCHAR(100), CONVERT(int, @a) % 1000, 2) + '.'
+ @result
WHILE( @a >= 1000 )
BEGIN
DECLARE @x int
SET @result = CONVERT(VARCHAR(100), CONVERT(int, FLOOR(@a / 1000))
% 1000, 2) + ',' + @result
SET @a = @a - FLOOR(@a / 1000)
END
RETURN @sign + @result
END
Regards,
Andras
April 18, 2008 at 1:24 pm
DECLARE @numreal BIGINT, @num BIGINT,
@Result VARCHAR(200)
SELECT @numreal = -123456789012,
@num = ABS(@numreal),
@Result = CASE(SIGN(@numreal)) WHEN 1 THEN '' ELSE '-' END
SELECT @result = @result + CASE x WHEN 1 THEN '' ELSE ',' END + SUBSTRING(CAST(@num AS VARCHAR),(3*(x-1)+1),3)
FROM tblTally WHERE x <= LEN(@num)/3
ORDER BY tblTally.X
PRINT @result
Not sure but I would suspect this may be a little faster.
April 18, 2008 at 1:28 pm
Actually, mine doesn't consider the fractional part of the number.
April 18, 2008 at 1:46 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION BigMoney
(
@value float
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @numreal BIGINT, @num BIGINT,
@Result VARCHAR(200)
SELECT @numreal = CAST(@value AS BIGINT),
@num = ABS(@numreal),
@Result = CASE(SIGN(@numreal)) WHEN 1 THEN '' ELSE '-' END
SELECT @result = @result + CASE x WHEN 1 THEN '' ELSE ',' END + SUBSTRING(CAST(@num AS VARCHAR),(3*(x-1)+1),3)
FROM tblTally WHERE x <= LEN(@num)/3
ORDER BY tblTally.X
IF @value != FLOOR(@value)
SELECT @result = @result + '.' + SUBSTRING(CAST(FLOOR(ABS(@value))-ABS(@value) AS VARCHAR),4,LEN(CAST(FLOOR(ABS(@value))-ABS(@value) AS VARCHAR))-2)
RETURN @result
END
GO
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1000 row(s) affected)
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 684 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
*/
SELECT TOP 1000 dbo.myformat(-123456.1122)
FROM tblTally
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1000 row(s) affected)
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 258 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
*/
SELECT TOP 1000 dbo.BigMoney(-123456.1122)
FROM tblTally
Looks like I have actually learned something on this site 🙂 better than 2x faster.
There was a minor bug that I had to edit (-2 vs -3) A little of the fraction was being cut off
April 18, 2008 at 2:13 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [BigMoney]
(
@value float
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @numreal BIGINT, @num BIGINT,
@Result VARCHAR(200)
SELECT @numreal = CAST(@value AS BIGINT),
@num = ABS(@numreal),
@Result = CASE(SIGN(@numreal)) WHEN 1 THEN '' ELSE '-' END
SELECT @result = @result + CASE x WHEN 1 THEN '' ELSE ',' END + SUBSTRING(CAST(@num AS VARCHAR),(3*(x-1)+1),3)
FROM tblTally WHERE x <= CASE LEN(@num)/3 WHEN 0 THEN 1 ELSE LEN(@num)/3 END
ORDER BY tblTally.X
IF @value != FLOOR(@value)
SELECT @result = @result + '.' + SUBSTRING(CAST(FLOOR(ABS(@value))-ABS(@value) AS VARCHAR),4,LEN(CAST(FLOOR(ABS(@value))-ABS(@value) AS VARCHAR))-2)
RETURN @result
END
There was another bug. 🙁 Fixed now. I think I have tested all cases.
April 18, 2008 at 2:46 pm
Of course I found another bug 🙁 Big Floats can't have more that 15 digits precision, however obviously 1E40 won't fit in a bigint. oops. Sorry, going home soon and I don't feel like working on this over the weekend. And Andras's works well (although it rounds in an unconventional way for decimal portion) and I have never seen ,'s to the right of the decimal.
However, I am sure both methods can be fixed to handle the rest of the possible cases.
Have fun.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply