formatting numbers

Hi,
I was wondering whether there is a way to format numbers in sql as below.
Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
So that 100 = 0100, 0999, 0022, 1222, etc...
Does anyone know how to do this?
Thanks
DhruvTry:
declare @.x int
set @.x = 121
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 100
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 999
select right('0000' + cast(@.x as varchar(4)), 4) num
- Vishal|||Try this:
declare @.i smallint
set @.i = 23 -- or ...
select RIGHT('000' + CAST(@.i AS varchar(4)), 4)
HTH
Vern
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||You could do soemthing like this in T-SQL:
declare @.i int
set @.i = 122
select right('0000' + cast(@.i as varchar(4)), 4)
But I'd question why you'd want to do this on SQL Server
side? This is best done at the clietn side in whatever
language you may be using. Most languages have good
support for this type of string manipulation.
Linchi
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||SELECT RIGHT('0000'+RTRIM(1), 4)
However, I agree with Linchi. Do your "prettifying" of the data where it
belongs, in the presentation tier.
> I was wondering whether there is a way to format numbers in sql as below.
> Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> So that 100 = 0100, 0999, 0022, 1222, etc...
> Does anyone know how to do this?
> Thanks
> Dhruv|||I don't know that I completely agree with that, though I see the point.
Consider the case where you use the same data 10 places, using the same
stored procedure. The formatting would be easier done in the procedure,
rather than the UI.
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GXS0IrjDHA.2312@.TK2MSFTNGP12.phx.gbl...
> SELECT RIGHT('0000'+RTRIM(1), 4)
>
> However, I agree with Linchi. Do your "prettifying" of the data where it
> belongs, in the presentation tier.
>
> > I was wondering whether there is a way to format numbers in sql as
below.
> >
> > Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> >
> > So that 100 = 0100, 0999, 0022, 1222, etc...
> >
> > Does anyone know how to do this?
> >
> > Thanks
> >
> > Dhruv
>|||> Consider the case where you use the same data 10 places, using the same
> stored procedure.
In most client applications, you can have a common formatting routine.|||Awesome
Thanks
"Vishal Parkar" <_vgparkar@.yahoo.co.in> wrote in message news:<#LU2$ArjDHA.744@.tk2msftngp13.phx.gbl>...
> Try:
> declare @.x int
> set @.x = 121
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 100
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 999
> select right('0000' + cast(@.x as varchar(4)), 4) num