Tibor is correct on the formatting, but you might need to carry it further -
if you need to pass negative numbers, the format will require the '-' on
either the left or the right, depending on the target format requirements.
you might need to do something like this sample:
else '-' + RIGHT('0000' +
If you run it, you'll get the general idea...it's important to verify the
size and format requirements of the target system for numeric fields,
especially if they are signed.
> No formatting function but you can add a bunch of zeroes and then do a
> right. something like:
> SELECT RIGHT('00000' + CAST(5 AS varchar(5)), 5)
> SELECT RIGHT('00000' + CAST(140 AS varchar(5)), 5)
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil at: http://www.sqlserverfaq.com
> Please reply to the newsgroup only, not by email.
> > Hi:
> > I am exporting fields from SQL Server 7 SP2 to a fixed field length text
> > file for a mainframe application. The mainframe app needs a number
> > be zero-filled, i.e. 140 should be 00140, 5 should be 00005. Is there a
> > formatting function in SQL Server to do this?
> > Thanks, Karen