|
|
Posted by Stu on 05/31/06 23:14
Just cause I've seen a couple of examples using CASE; I use trick
similar to your second example:
SELECT RIGHT('0' +CONVERT(varchar(2), @number), 2)
Granted, it only works on a two-digit number, but it saves typing. The
REPLICATE idea is pretty smooth, though.
Stu
Tony Rogerson wrote:
> Hi Angellian,
>
> For 2 character string you can just use CASE...
>
> declare @number tinyint
> set @number = 2
>
> select case when @number between 0 and 9 then '0' else '' end + cast(
> @number as varchar(2) )
>
> Otherwise, if your resultant string needs to be bigger than 2 characters do
> this...
>
> declare @number int
> declare @string varchar(10)
> declare @size_of_fixed_string tinyint
> set @size_of_fixed_string = 10
> set @number = 40
>
> print replicate( '0', @size_of_fixed_string )
>
> set @string = left( replicate( '0', @size_of_fixed_string ),
> @size_of_fixed_string - len( @number ) ) + cast( @number as varchar(10) )
>
> print @string
>
> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/29/765.aspx
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
>
> <angellian@gmail.com> wrote in message
> news:1148779910.070643.296910@g10g2000cwb.googlegroups.com...
> > Sorry to raise a stupid question but I tried many methods which did
> > work.
> > how can I conserve the initial zero when I try to convert STR(06) into
> > string in SQL statment?
> > It always gives me 6 instead of 06.
> >
> > Thanks a lot.
> >
Navigation:
[Reply to this message]
|