Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug