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
>