Display leading zeros

I want my SQL 2005 output to display leading zeros up to a total of 6
characters. Foe example, the number 23 would display 000023, 2000 would
display 002000, etc. Is there a "Fill" type of command or can I do this in
a CAST function? Thanks.
DavidHi,
no there isn=B4t but you can you this snippet of mine to achieve it:
DECLARE @.SomeValue INT
SET @.SomeValue =3D 34
DECLARE @.TrailingDigits INT
SET @.TrailingDigits =3D 7
SELECT RIGHT(
REPLICATE('0',@.TrailingDigits) +
CONVERT(VARCHAR(200),@.SomeValue)
,@.TrailingDigits
)
I think you should be smart enough to put a function around it, right
:-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||David,
better to format the output in the client application. Try:
declare @.i int
set @.i = 23
select right(replicate('0', 6) + ltrim(@.i), 6)
go
AMB
"David" wrote:

> I want my SQL 2005 output to display leading zeros up to a total of 6
> characters. Foe example, the number 23 would display 000023, 2000 would
> display 002000, etc. Is there a "Fill" type of command or can I do this i
n
> a CAST function? Thanks.
> David
>
>|||Here is one option. I had to jump through a few hoops because I stored 23
in an int column....
DECLARE @.foo table (col1 int)
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(CONVERT(varchar(6),col1))) +
CONVERT(varchar(6),col1) FROM @.foo
if your data is stored as varchar you can do something like this:
DECLARE @.foo table (col1 varchar(6))
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(col1)) + col1 FROM @.foo
Keith Kratochvil
"David" <dlchase@.lifetimeinc.com> wrote in message
news:%23NOVduNXGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I want my SQL 2005 output to display leading zeros up to a total of 6
>characters. Foe example, the number 23 would display 000023, 2000 would
>display 002000, etc. Is there a "Fill" type of command or can I do this in
>a CAST function? Thanks.
> David
>|||That worked! Thanks.
David
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7D66A28-CB7F-4EC1-948D-A9F5BE29BD0D@.microsoft.com...
> David,
> better to format the output in the client application. Try:
> declare @.i int
> set @.i = 23
> select right(replicate('0', 6) + ltrim(@.i), 6)
> go
>
> AMB
> "David" wrote:
>