Format numbers in a view

I need to show the following numbers as such in a view (in a 15.3 format)...
12.3 as 000000000000012.300
10 as 000000000000010.000
12.3367 as 000000000000012.337
Is there an easy way to do this using a Cast ?
Thanks !Not on 2000 AFAIK. Only thing I can think of is converting to string.
Offcourse, real question is why do you format values on SQL Server? You
should do it in presentation layer.
MC
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||print replicate( '0', 19 - len( cast( cast( round( 12.3367, 3 ) as
decimal( 25, 3 ) ) as varchar(30) ) ) ) +
cast( cast( round( 12.3367, 3 ) as decimal( 25, 3 ) ) as varchar(30) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||I've needed to do this type of thing a lot of times because of fixed length
data feeds, its less maintanence to just use SQL Server, otherwise you'd
need to write some code using something like C# or something which imho is
complete over kill and adds to life cycle burden (dev, support,
maintanence).
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23UTFc8GZGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Not on 2000 AFAIK. Only thing I can think of is converting to string.
> Offcourse, real question is why do you format values on SQL Server? You
> should do it in presentation layer.
>
> MC
>
> "Rob" <rwchome@.comcast.net> wrote in message
> news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>|||Rob
declare @.d decimal (25,3)
set @.d=12.3367
select right('0000000000000000000'+cast(@.d as varchar(19)),19)
Or you can use REPLICATE function as Tony suggested
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||Thanks Tony... but I get Invalid or Missing Expression when i place the
code into a column of a view... any ideas ?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u62Xp9GZGHA.4936@.TK2MSFTNGP05.phx.gbl...
> print replicate( '0', 19 - len( cast( cast( round( 12.3367, 3 ) as
> decimal( 25, 3 ) ) as varchar(30) ) ) ) +
> cast( cast( round( 12.3367, 3 ) as decimal( 25, 3 ) ) as varchar(30) )
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Rob" <rwchome@.comcast.net> wrote in message
> news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>|||Hmm, I'll have to think about that. I was concerned about performanse
issues, perhaps too concerned :).
MC
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23ep4e$GZGHA.4752@.TK2MSFTNGP02.phx.gbl...
> I've needed to do this type of thing a lot of times because of fixed
> length data feeds, its less maintanence to just use SQL Server, otherwise
> you'd need to write some code using something like C# or something which
> imho is complete over kill and adds to life cycle burden (dev, support,
> maintanence).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23UTFc8GZGHA.3936@.TK2MSFTNGP05.phx.gbl...
>|||Tony, never mind... I did not drop the Print statement ... My Bad !
Thanks, Rob
"Rob" <rwchome@.comcast.net> wrote in message
news:WvadnRdnPONf5trZRVn-gg@.comcast.com...
> Thanks Tony... but I get Invalid or Missing Expression when i place the
> code into a column of a view... any ideas ?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:u62Xp9GZGHA.4936@.TK2MSFTNGP05.phx.gbl...
>|||C'mon fess-up,I doubt you were really thinking 'performance':)
But if you really were then you are really opening pandora's box on your
client and closing it on the server:)
'why do you format values on SQL Server' - this sounds like a Celkoism,
sounds good but is silly:)
www.rac4sql.net
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:e53wxQHZGHA.5116@.TK2MSFTNGP03.phx.gbl...
> Hmm, I'll have to think about that. I was concerned about performanse
> issues, perhaps too concerned :).
> MC
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23ep4e$GZGHA.4752@.TK2MSFTNGP02.phx.gbl...
otherwise
>|||A) I DID think about performance and usage of indexes and execution plans
and ordering and stuff that goes to hell with converting. If you dont, well,
your choice.
B) client can and should handle formatting, not just because it can but
there are reasons such as localizing, string manipulation and similar
'small' stuff that client can handle better then sql server. If it is silly,
perhaps you would care to explain further? I feel a bit stupid at the
moment.
If you just said I worry too much about theoretical side and too little
about practical side I would certainly agree that it is possible.
MC
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:Oa8115OZGHA.4944@.TK2MSFTNGP02.phx.gbl...
> C'mon fess-up,I doubt you were really thinking 'performance':)
> But if you really were then you are really opening pandora's box on your
> client and closing it on the server:)
> 'why do you format values on SQL Server' - this sounds like a Celkoism,
> sounds good but is silly:)
> www.rac4sql.net
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:e53wxQHZGHA.5116@.TK2MSFTNGP03.phx.gbl...
> otherwise
>

Format Numbers

How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123
If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123

Format Numbers

Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
jouj
use cast and convert functions
see BOL
"jouj" wrote:

> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
|||See:
http://groups-beta.google.com/group/...d?dmode=source
Anith
|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
P.RUELLO
DBA
"jouj" wrote:

> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>
|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>

Format Numbers

I have a report that returns a column with number values that I want to round
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>

Format Numbers

How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123

Format Numbers

Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/97af3e2b3f45b72d?dmode=source
--
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>

Format Numbers

Hi all,
how to format numbers in T-SQL, is there any function similar to Format in
VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
Thanks in advance.
Regards
joujuse cast and convert functions
see BOL
"jouj" wrote:

> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||See:
f3e2b3f45b72d?dmode=source" target="_blank">http://groups-beta.google.com/group...2d?dmode=source
Anith|||The STR() function exists. But you can make your own formats using a udf
(you can have some examples on sqlservercentral for example)
Best regards
--
P.RUELLO
DBA
"jouj" wrote:

> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>|||FYI, you're usually much better off using the formatting functions of
whatever front-end tool you're using to access SQL Server with.
Mike
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:6B47D07F-0B21-4B9B-9D08-1A0194287DCB@.microsoft.com...
> Hi all,
> how to format numbers in T-SQL, is there any function similar to Format in
> VBA? (ex: Format(5236.25,"#,##0.00")==> 5,236.25)
> Thanks in advance.
> Regards
> jouj
>