Menu
I know it could be done trivially in a non-SQL environment [post-data processing, frontend, what have you], but that's not possible at the moment. Is there a way to take a decimal(5,2) and convert it to a varchar without the trailing zeroes/decimal points? For example:
declare @number decimal(5,2)
set @number = 123.00
select cast(@number as varchar) as FormattedNumber
And the result is '123.00'. Is there a (simple) way to get '123' instead? And likewise, instead of '123.30', '123.3'? Could do it by figuring out whether or not the hundredths/tenths places were 0 and manually trimming characters, but I wanted to know if there was a more elegant solution.
643
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
To store numbers that have fixed precision and scale, you use the DECIMAL data type. In this syntax: p is the precision which is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision has a range from 1 to 38.
MySQL FORMAT() FunctionThe FORMAT() function formats a number to a format like "#,###,###. ##", rounded to a specified number of decimal places, then it returns the result as a string.
What about:
SELECT CAST(CAST(@number AS float) AS varchar(10))
However you may want to test this carefully with your raw data first.
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
