is there a built in function in sql server 2005 that will format a number
with commas as the thousand-separator?
ie., here's what i want to do:
input=1000
output=1,000
or
input=1000000
output=1,000,000
if there isn't a built-in function, can someone point me to a
user-defined-function that might do the trick?
thanks so much,
jt
BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT OPTIONS AVAILABLE ARE: 1234 1,234.00 (this one is close, but not what i want- this particular number will never have decimals so i don't want to waste space showing ".00") 1234.00 1.234123e+003 its not helpful to reply when you don't have an answer, b/c then other people assume, by seeing a response next to the posting, that the problem has been solved. [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:unj7UsLQHHA.4172@TK2MSFTNGP03.phx.gbl...
> VB has format()
> VBScript has formatnumber()
> C# has string.format()
>
> Why do you need to perform this presentation layer task in the database?
>
>
>
>
>
> "JTL" <jt@clickstreamtech.com> wrote in message
> news:OYfCLpLQHHA.992@TK2MSFTNGP06.phx.gbl...
>> is there a built in function in sql server 2005 that will format a number
>> with commas as the thousand-separator?
>>
>> ie., here's what i want to do:
>> input=1000
>> output=1,000
>>
>> or
>> input=1000000
>> output=1,000,000
>>
>> if there isn't a built-in function, can someone point me to a
>> user-defined-function that might do the trick?
>>
>> thanks so much,
>>
>> jt
>>
>>
>>
>>
>
>
its not helpful to reply when you don't have an answer, b/c then other people assume, by seeing a response next to the posting, that the problem has been solved. You're absolutely right. And every time I see that Aaron has posted a response, I open it to learn something new. Vern Rabe [quoted text, click to view] "JTL" wrote:
> BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT
> OPTIONS AVAILABLE ARE:
>
> 1234
> 1,234.00 (this one is close, but not what i want- this particular number
> will never have decimals so i don't want to waste space showing ".00")
> 1234.00
> 1.234123e+003
>
> its not helpful to reply when you don't have an answer, b/c then other
> people assume, by seeing a response next to the posting, that the problem
> has been solved.
>
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:unj7UsLQHHA.4172@TK2MSFTNGP03.phx.gbl...
> > VB has format()
> > VBScript has formatnumber()
> > C# has string.format()
> >
> > Why do you need to perform this presentation layer task in the database?
> >
> >
> >
> >
> >
> > "JTL" <jt@clickstreamtech.com> wrote in message
> > news:OYfCLpLQHHA.992@TK2MSFTNGP06.phx.gbl...
> >> is there a built in function in sql server 2005 that will format a number
> >> with commas as the thousand-separator?
> >>
> >> ie., here's what i want to do:
> >> input=1000
> >> output=1,000
> >>
> >> or
> >> input=1000000
> >> output=1,000,000
> >>
> >> if there isn't a built-in function, can someone point me to a
> >> user-defined-function that might do the trick?
> >>
> >> thanks so much,
> >>
> >> jt
> >>
> >>
> >>
> >>
> >
> >
>
>
First of all, Aaron's answer was perfect given the amount of description of the issue in the original posting. Since Aaron's reply has now elucidated from you that your issue is in Microsoft Reporting Services (the presentation layer in your system), there is no better demonstration that his reply was perfect at pushing YOU to think through your system and accurately identify where the problem is. Now that we understand where the issue is, I found the following in BOL in seconds: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/7852b4c7-3b45-406d-8e43-69c8cf2b710e.htm Formatting Numeric and Date Values in a Report You can specify a format for numeric and date values by updating the Format property of its text box with a formatting string. For example, you can set a text box for a numeric field to display the number as currency. Reporting Services uses Microsoft .NET Framework formatting strings or you can create a custom formatting string for the Format property. .... You can modify many of the format strings to include a precision specifier that defines the number of digits to the right of the decimal point. For example, a formatting string of D0 formats the number so that it has no digits after the decimal point. You can also use custom formatting strings, for example, #,###. Have you tried defining a custom formatting string as per the manual? [quoted text, click to view] On Jan 25, 3:23 pm, "JTL" <j...@clickstreamtech.com> wrote:
> BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT
> OPTIONS AVAILABLE ARE:
>
> 1234
> 1,234.00 (this one is close, but not what i want- this particular number
> will never have decimals so i don't want to waste space showing ".00")
> 1234.00
> 1.234123e+003
>
> its not helpful to reply when you don't have an answer, b/c then other
> people assume, by seeing a response next to the posting, that the problem
> has been solved.
>
> "Aaron Bertrand [SQL Server MVP]" <ten....@dnartreb.noraa> wrote in messagenews:unj7UsLQHHA.4172@TK2MSFTNGP03.phx.gbl...
>
>
>
> > VB has format()
> > VBScript has formatnumber()
> > C# has string.format()
>
> > Why do you need to perform this presentation layer task in the database?
>
> > "JTL" <j...@clickstreamtech.com> wrote in message
> >news:OYfCLpLQHHA.992@TK2MSFTNGP06.phx.gbl...
> >> is there a built in function in sql server 2005 that will format a number
> >> with commas as the thousand-separator?
>
> >> ie., here's what i want to do:
> >> input=1000
> >> output=1,000
>
> >> or
> >> input=1000000
> >> output=1,000,000
>
> >> if there isn't a built-in function, can someone point me to a
> >> user-defined-function that might do the trick?
>
> >> thanks so much,
>
> >> jt- Hide quoted text -- Show quoted text -
[quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> VB has format()
> VBScript has formatnumber()
> C# has string.format()
And Delphi has FloatToStrF And I agree with you :) -- Kind regards,
[quoted text, click to view] JTL wrote:
> BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT
> OPTIONS AVAILABLE ARE:
>
> 1234
> 1,234.00 (this one is close, but not what i want- this particular
> number will never have decimals so i don't want to waste space
> showing ".00") 1234.00 1.234123e+003
I'm sure this must be possible in Reporting services! I'm gonna look at it later tonight, gotta go now. [quoted text, click to view] > its not helpful to reply when you don't have an answer, b/c then
> other people assume, by seeing a response next to the posting, that
> the problem has been solved.
And it's also not polite to start shouting at someone who is trying to help. -- HTH,
[quoted text, click to view] JTL wrote:
> BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT
> OPTIONS AVAILABLE ARE:
>
> 1234
> 1,234.00 (this one is close, but not what i want- this particular number
> will never have decimals so i don't want to waste space showing ".00")
> 1234.00
> 1.234123e+003
>
> its not helpful to reply when you don't have an answer, b/c then other
> people assume, by seeing a response next to the posting, that the problem
> has been solved.
>
It's not helpful to post questions and then argue about the answers before you've researched the tools that you're using: http://msdn2.microsoft.com/en-US/library/ms157406.aspx See the part about custom format strings... -- Tracy McKibben MCDBA
VB has format() VBScript has formatnumber() C# has string.format() Why do you need to perform this presentation layer task in the database? [quoted text, click to view] "JTL" <jt@clickstreamtech.com> wrote in message
news:OYfCLpLQHHA.992@TK2MSFTNGP06.phx.gbl...
> is there a built in function in sql server 2005 that will format a number
> with commas as the thousand-separator?
>
> ie., here's what i want to do:
> input=1000
> output=1,000
>
> or
> input=1000000
> output=1,000,000
>
> if there isn't a built-in function, can someone point me to a
> user-defined-function that might do the trick?
>
> thanks so much,
>
> jt
>
>
>
>
ok ok- i'm sorry to have started the argument-thank you all for helping solve my problem. aaron, im also sorry about yelling in my response- that was rude, but i was frustrated at the moment, and it has been my experience that when a response is posted that does not answer the question at hand, the likelihood of getting a valid answer dramatically decreases. obviously, this was not the case in this posting. at any rate, by applying the formatting string "N0", i was able to get the exact format i needed. i had researched the issue prior to posting, but had not been thorough enough. the example in BOL describes that "a formatting string of D0 formats the number so that it has no digits after the decimal point." In my hastiness, I didn't realize that this same logic could be applied to the "N" formatting string, as well. [quoted text, click to view] "Stijn Verrept" <TURN_moc.tfosyrtne@njits_AROUND> wrote in message
news:X6qdnTwaC6zajyTYnZ2dnUVZ8tignZ2d@scarlet.biz...
> Aaron Bertrand [SQL Server MVP] wrote:
>
>> VB has format()
>> VBScript has formatnumber()
>> C# has string.format()
>
> And Delphi has FloatToStrF
>
> And I agree with you :)
>
>
> --
> Kind regards,
>
> Stijn Verrept.
[quoted text, click to view] > its not helpful to reply when you don't have an answer,
Well, maybe you can't see that my question was meant to derive more information to help you better. [quoted text, click to view] > b/c then other people assume, by seeing a response next to the posting,
> that the problem has been solved.
Maybe you assume that, but I don't know anyone who does. And since you'd rather yell at me than do your own research, good luck with your problem!
[quoted text, click to view] JTL wrote:
>is there a built in function in sql server 2005 that will format a number
>with commas as the thousand-separator?
>
declare @test as money set @test = 19250500 select @test, left(convert(varchar(20), @test, 1), len(convert(varchar(20), @test, 1)) -3) or declare @test as decimal set @test = 19250500 select @test, left(cast(convert(varchar(20),cast(@test as money),1) as varchar), len(convert(varchar(20),cast(@test as money),1)) -3)
Don't see what you're looking for? Try a search.
|
