Post reply

Datatype conversion

  • August 20, 2013 at 4:58 pm

    #395332

    How do i convert nvarchar datatype column to float? The only problem is nvarchar column has non-numeric values like names.

  • August 20, 2013 at 5:02 pm

    #1643265

    What do you want to do with non-numeric values?

    What are you going to do with this data?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 20, 2013 at 5:22 pm

    #1643266

    Right now, the numeric values in that column are like (345.678987689). I'd like to change these values to 3 decimal places. For example(345.678).

    Since this column also has non-numeric values, the conversion is failing.

    Error message "Cannot convert Nvarchar to numeric"

    Can someone help me with converting the big numeric values in the column to just 3 numbers after decimal point?

  • August 21, 2013 at 2:11 am

    #1643350

    in that case how will you store non numeric values in a decimal field?

    try using replace and update it on same field, if you want to use the same column as it is.

    Regards
    Durai Nagarajan

  • August 21, 2013 at 2:27 am

    #1643354

    Of course: the best way is to design the table/columns in a way that strings and numeric values are not in the same column.

    But I guess you just have to cope with it for now. You could first copy all numeric values (using something like: WHERE {column} NOT LIKE '[a-z]%' ) to a temporary table. Include some ID field(s) to be able to join them with the original table in the last step. Alter the values in this temporary table (using CAST/CONVERT) to the decimal format. And finally update the original table with the values from the temporary table (using INNER JOIN and cast/convert the numeric values back to a string).

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • August 21, 2013 at 2:52 am

    #1643356

    Sounds like you have design issues there with mixing of datatypes.

    Could try using an ISNUMERIC function to see if the data is numeric then rtrim and some pattern matching to locate the decimal point and reduce the decimal down to 3 char's.

    Your never going to be able to just convert the column to float all the time you have mixed datatypes.

  • August 21, 2013 at 2:52 pm

    #1643664

    Issue resolved.

    I built a cursor to accomplish this. May not be the best choice but it worked for me.

    Thanks guys.

Viewing 7 posts - 1 through 6 (of 6 total)

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