Numeric fixed length field with bcp out
Posted on 2003-12-05
I am in the process of bcp out a file to be converted into IBM Mainframe. I need all fields within the table to be fixed length output with the bcp out. I have created a view to convert varchar to char and used isnull function to ensure dates and char fields that contain null values adhere to fixed length. Problem or confession: I have several numeric fields that contain null values. IE) Col3 numeric(12,2) null
#1 Field contains all null values and I need to represent fixed length...What can I do?
select convert(char(13),isnull(convert(varchar, Col2),'0000000000.00')) as Col2 from
This would fill the numeric field with 000000000.00 filling the numeric(12,2) precision,scale
Any other ideas or better solutions for issue #1 would be greatly appreciated!!!
Table has field numeric(12,2) null
Column contains both null values and data
--I have used this syntax, but it does not represent the 2 scale. If there was some numeric
data in this column it would look like this 10.00
select right(replicate("0000000000",10) + convert(varchar, Col2), 12) from TestNumeric
I am missing something because here is my output....
rows 1-4 contain data, row 5 is null
There should always be 10 bytes represented in the precision and 2 in scale
Correct ME IF I AM WRONG
ie)Fixed Length for numeric(12,2)----> 0000000000.00
Any help is greatly appreciated!!!!