asked on

SQL and 9(9)V9(6) numeric format.

I am writing a vb.net app that interfaces with a sql server 2005 database.I am pulling data out of it and writing it to a flat file for delivery to an outside vendor. Some of the numeric fields require what they call the 9(9)V9(6) format. Is anyone familar with this and if so is it possible to format the field this way in my sql query?

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfsedlar3rd

ASKER

You are right on LowFatSpread they do not want a decimal point.

jfsedlar3rd

ASKER

So if they were asking also for 9(13)V9(2), How would that change the syntax.

jfsedlar3rd

ASKER

I just tested it and I may be mistaken but I thought the idea for this was to gain the same field length regardless of what the amount was. I ask this because in my output file some values are longer 9, 10 and 11 characters in length. The file this value goes into is of fixed length delimitation.

Lowfatspread

Substring(REPLACE(REPLACE(convert(char(16),convert(decimal(15,2) , yourcolumn) ),'-',' '),'.',''),1,15)

for 9(13)V99 it becomes decimal(15,2) ie total digits , number of decimals within the number of digits...

use substring to get a fixed length...