Formatting numbers (commas and decimal)
-
I want to create a SQL statement to retrieve a numeric value from a table, and return it with the commas seperating the thousand fields, and return the number of decimal places that I specify.
Example:
Select Balance from tbl_Loan
12,345,678.00 instead of 12345678.0000
I am using MS-SQL, and I would like to have it run in the Query Analyzer.
This is the only place where I will have the ability to format this number.
Thank you,
Bryan Clauss
-
LEFT(CONVERT(Char(12), CAST(MyNumber AS Money), 1) , 10)
By manipulating the LEFT and the CAST you can control size and # of decimals
KlK -
I have been able to limit my decimal points to 2 places by using the following code:
LTRIM( STR ( MyNumber, 99, 2 ) )
I am mostly trying to determine a way to add my commas.
Thanks,
Bryan Clauss
-
Do a search here in the fora or in the script section. IIRC hat Antares686 posted a solution to your problem some time ago.
Btw, you are aware of the fact that this is merely a presentational issue and that QA isn't the tool of choice for presenting data to common users?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
After confirming what Frank says about 'resentation issue...' I just add that sometimes a 'vb format in tsql' can be usefule...
So take a look at:
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-48-formatnumber.htm
HTH
Gigi
-
I'm not sure if this will help, but here's a script I wrote, followed by a bunch of examples:
DROP FUNCTION dbo.FormatDec
GO
CREATE FUNCTION dbo.FormatDec
(
@num decimal(38,4),
@decPlaces int
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @cnum varchar(50), @len int, @pos int, @fraction varchar(50)
DECLARE @numCommas int
SET @cnum = CONVERT(varchar(50), @num)
SET @fraction = ''
SET @pos = CharIndex('.', @cnum, 1)
IF @pos > 0
BEGIN
SET @fraction = Substring(@cnum, @pos + 1, 50)
SET @cnum = Left(@cnum, @pos - 1)
END
SET @len = Len(@cnum)
SET @numCommas = (@len-1) / 3
WHILE @len > 3
BEGIN
SET @cnum = Stuff(@cnum, @len-2, 0, ',')
SET @len = @len - 3
END
IF @decPlaces > 0
BEGIN
SET @fraction = Left(@fraction + Replicate('0', @decPlaces), @decPlaces)
END
ELSE
SET @fraction = ''
IF @fraction <> ''
SET @cnum = @cnum + '.' + @fraction
RETURN @cnum
END
go
print dbo.FormatDec(0,2)
print dbo.FormatDec(1,2)
print dbo.FormatDec(12,2)
print dbo.FormatDec(123,2)
print dbo.FormatDec(1234,2)
print dbo.FormatDec(12345,2)
print dbo.FormatDec(123456,2)
print dbo.FormatDec(1234567,2)
print dbo.FormatDec(1234567,0)
print dbo.FormatDec(1234567,1)
print dbo.FormatDec(1234567,2)
print dbo.FormatDec(1234567,3)
print dbo.FormatDec(12345678,2)
print dbo.FormatDec(123456789,2)
print dbo.FormatDec(1234567890,2)
print dbo.FormatDec(2147483647,2)
-
I use this quick and dirty function (emphasis on adjectives) for integer and decimal values. You must convert the number to a string format before calling this function (although it will automatically cast with the call).
Use Common
If Object_Id('dbo.NumFmt') is not Null Drop Function dbo.NumFmt
Go
Create Function NumFmt
(
@STR VarChar(256)
)
Returns VarChar(256)
As Begin
Declare
@i Int,
@StrLen Int,
@l VarChar(256),
@r VarChar(256)
Select
@STR=LTrim(RTrim(IsNull(@Str,''))),
@StrLen=Len(@Str),
@i=CharIndex('.',@Str),
@i=Case @i when 0 then @StrLen else @i-1 End,
@l=Left(@Str,@i),
@r=Right(@Str,Case @StrLen-@i when 0 then 0 else @StrLen-@i-1 End),
@i=Len(@l)+1
While @i>4 Select @i=@i-3,@l=Stuff(@l,@i,0,',')
Return @l+Case @r when '' then '' else '.' End+@r
End
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply