Export SQL to text file - number formatting

Export SQL to text file - number formatting

Post by NCRosemar » Tue, 17 Apr 2001 04:44:57



 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 field to
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

 
 
 

Export SQL to text file - number formatting

Post by Tibor Karasz » Tue, 17 Apr 2001 04:50:14


Karen,

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.


Quote:

>  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 field
to
> 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


 
 
 

Export SQL to text file - number formatting

Post by Meade Robbo » Tue, 17 Apr 2001 10:39:46


karen,

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.
So...

you might need to do something like this sample:





                                        else '-' + RIGHT('0000' +

                                        end

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.

-Meade



> Karen,

> 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
field
> to
> > 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

 
 
 

Export SQL to text file - number formatting

Post by NCRosemar » Tue, 17 Apr 2001 19:25:49


Thanks Tibor and Meade!
Karen