Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Alter Column data type from float to varchar - Comma changed to decimal?

Answered Alter Column data type from float to varchar - Comma changed to decimal?

  • Saturday, February 16, 2013 5:17 PM
     
     
    Sign In to Vote
    0

    Hi

    I have a column with data type float, values: 0,4 , 5,6 , 98, 0,242424 , etc

    Basically, floating point values with "comma" as seperator. When i alter the column datatype to varchar, the comma seperator is changed to : (decimal). I want to maintain the comma seperator. Any clues?

    Ps: Regional settings already have "comma" as the seperator. Also If i cast or convert table values e.g. CAST 0,4 to varchar, its the same result: 0.4. Also I don't want to do any changes in presentation layer)

    Thanks in advance.

     

All Replies

  • Saturday, February 16, 2013 5:30 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Simplest solution is the REPLACE function:

    http://msdn.microsoft.com/en-us/library/ms186862.aspx

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


     
  • Sunday, February 17, 2013 5:42 AM
     
     Answered
    Sign In to Vote
    0

    You can retain any commas, because there are no commas to retain. What you see in SSMS or whereever you look is just a textual representation of a binary value. A floating point valuem consists of a mantissa of 53 bits, and the rest is a power of 2 to be multiplied with the mantissa.

    When you cast a float value instide SQL Server to character, the decimal separator will always be a decimal point. Well, if you are on SQL 2012, you can use the new format() function to format according to a certain culture.

    It's not clear to me why you want to alter the column to varchar, but you will get a decimal point. If you don't like that, you will need to use the repace() function as Kalman suggested.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se