Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: numeric to nvarchar

    Hi,

    This is what I have. I have a table with 4 numeric field. So when I insert into those values information that is null the result will be 0.0000000 due to the numeric declared fields

    I do not want this, I would like to make this 0.000000 become blank.

    So what I have done is to create another table exactly with the same values, and declare this 4 fields as nvarchar instead of numeric. I then did and export directly from the store procedure, but I still get 0.00000 in those fields where there is null values rather than getting just blank.

    Does anyone knows how to fix this issue.?

    Thanks in advance
    Last edited by buzmay; 03-06-12 at 04:18.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have a table with 4 numeric field. So when I insert into those values information that is null the result will be 0.0000000 due to the numeric declared fields
    What you see depends on the tool you use to show the contents of those columns. Sometimes NULL values are not displayed (blanc cell, empty text), sometimes as "-", sometimes as "null", ...

    What tool are you using? Are those columns mandatory? Do they have a DEFAULT 0? Normally empty columns are not displayed as 0.000000. When you execute
    Code:
    SELECT ColumnName FROM MyTable WHERE ColumnName IS NULL
    What is displayed?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    I will try to fix this myself I think I can and will come back with the answer. Is too long and complicated for me to explain this in a good way in English.
    Thanks for the help Wim

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If your column is to only contain numeric data do not store it in a character field.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    I thought I had it but no.

    What I need is to change the values of 4 fields so that I get them with blank rather than 0.000
    So I have created a new table with these fields as nvarchar and was thinking to insert into this table the data so I get blank rather than 0.000

    something like
    Code:
     
    
    update table data
    if amount = 0
    begin 
    
    set amount = ' '
    
    end
    do you have any comments on how to do this correct?

    Thanks in advance

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by buzmay View Post
    So I have created a new table with these fields as nvarchar and was thinking to insert into this table the data so I get blank rather than 0.000
    ...
    do you have any comments on how to do this correct?
    Bad idea. Just don't do it.

    What was the result of the script I asked you to run in post#2?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are confusing storage with presentation, and the two are only casually related.

    What you need to do is to store the data appropriately (as numeric columns with zeros in them), then use a report writing tool such as SQL Server Reporting Services for a server based approach or http://www.microsoftstore.com/Access_2010 for a client based approach. The report writing tool will allow you to easily "zero suppress" the presentation of your data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Would setting the default value on the column be helpful?

  9. #9
    Join Date
    Jan 2012
    Posts
    52
    sorry i answered so late, i fix this by updating the table and creating a view million thanks guys you are great

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •