Saturday, February 16, 2013 5:17 PM
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.
Saturday, February 16, 2013 5:30 PMModerator
Simplest solution is the REPLACE function:
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Saturday, February 16, 2013 5:31 PM
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Sunday, February 17, 2013 4:37 AM
- Marked As Answer by Iric WenModerator Monday, February 25, 2013 9:32 AM
Sunday, February 17, 2013 5:42 AM
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, email@example.com