Post reply

Insert empty string(' ') in a decimal field

  • July 13, 2015 at 7:22 am

    #398735

    Hi

    I have a requirement where i have to insert empty string in column whose datatype is decimal.

    I have to replace the column value if it is null then insert it as ' '.

    Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.

    IIF(Column1 IS NULL, ' ', Column1))

    I used TRY_PARSE but it insert null value if there is conversion error.

    Thanks

  • July 13, 2015 at 7:28 am

    #1811961

    SQL006 (7/13/2015)

    Hi

    I have a requirement where i have to insert empty string in column whose datatype is decimal.

    I have to replace the column value if it is null then insert it as ' '.

    Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.

    IIF(Column1 IS NULL, ' ', Column1))

    I used TRY_PARSE but it insert null value if there is conversion error.

    Thanks

    Plain and simple, you can't. The datatype is decimal, it stores decimal values. There is no decimal value that is the same as an empty string. This is what NULL is for. You have no value for this column therefore NULL is the appropriate value. The only other option, which is NOT what I would recommend, is to change the datatype to varchar so you can insert an empty string.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 13, 2015 at 7:41 am

    #1811969

    Sean Lange (7/13/2015)

    Plain and simple, you can't. The datatype is decimal, it stores decimal values. There is no decimal value that is the same as an empty string. This is what NULL is for. You have no value for this column therefore NULL is the appropriate value. The only other option, which is NOT what I would recommend, is to change the datatype to varchar so you can insert an empty string.

    I'm doubtful that's the "only other option". Surely, this is a reporting scenario, where a blank needs to appear in the output instead of whatever it is that's annoying someone. That's a matter of using the reporting tool's abilities, or casting the decimal field to a varchar for output purposes, and using the ISNULL function to substitute an empty string for NULL values. I suspect the output is going to Excel, but we'll need the original poster to chime in and let us know the WHY behind the problem, in sufficient detail.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 13, 2015 at 7:55 am

    #1811979

    sgmunson (7/13/2015)

    Sean Lange (7/13/2015)

    Plain and simple, you can't. The datatype is decimal, it stores decimal values. There is no decimal value that is the same as an empty string. This is what NULL is for. You have no value for this column therefore NULL is the appropriate value. The only other option, which is NOT what I would recommend, is to change the datatype to varchar so you can insert an empty string.

    I'm doubtful that's the "only other option". Surely, this is a reporting scenario, where a blank needs to appear in the output instead of whatever it is that's annoying someone. That's a matter of using the reporting tool's abilities, or casting the decimal field to a varchar for output purposes, and using the ISNULL function to substitute an empty string for NULL values. I suspect the output is going to Excel, but we'll need the original poster to chime in and let us know the WHY behind the problem, in sufficient detail.

    There really is no option for putting an empty string into a decimal. It just won't work. That is what the OP stated they need to do. I too assume this is a reporting scenario and putting NULL in the column is the appropriate thing to do.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply