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?
jfsedlar3rdAsked:
Who is Participating?
 
LowfatspreadCommented:
Hello jfsedlar3rd,

thats a cobol picture clause format
and implies a unsigned number with 9 numbers before an implied decimal point and 6 places of decimals

so

  LEFT(REPLACE(REPLACE(convert(char(16),convert(decimal(15,6) , yourcolumn) ),'-',' '),'.',''),15)

SHOULD BE THE BASIC REQUIREMENT

however get them to confirm that they do not want :-
a physical decimal point
any sign information along with the numbers
and a character format


and to confirm that


Regards,

Lowfatspread
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in order to avoid any confusion, can you explain what that format is supposed to do, with data samples?
is this to be fixed-width, for example...
0
 
jfsedlar3rdAuthor Commented:
You are right on LowFatSpread they do not want a decimal point.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jfsedlar3rdAuthor Commented:
So if they were asking also for 9(13)V9(2), How would that change the syntax.
0
 
jfsedlar3rdAuthor Commented:
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.
0
 
LowfatspreadCommented:
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...

 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.