converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury