Solved

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

Posted on 2007-07-24
935 Views
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?
0
Question by:jfsedlar3rd

LVL 142

Assisted Solution

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

LVL 50

Accepted Solution

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
and a character format

and to confirm that

Regards,

0

Author Comment

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

Author Comment

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

Author Comment

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

LVL 50

Expert Comment

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

Featured Post

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!