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

Posted on 2007-07-24
Last Modified: 2008-01-09
I am writing a 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?
Question by:jfsedlar3rd
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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...
    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


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


    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



    Author Comment

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

    Author Comment

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

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


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    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 ( 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…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now