Menu
I need to convert data of a table and do some manipulation.
One of the column datatypes is Varchar, but it stores decimal numbers.
I am struggling to convert the varchar into decimal.
I have tried CAST( @TempPercent1 AS DECIMAL(28, 16))
Problem is that data also has some values in exponential notation, for example: 1.61022e-016.
The sql query is throwing error on encountering such value.
The error is Error converting data type varchar to numeric.
How should I handle exponential notation values during varchar to decimal conversion?
653
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
That “E” means “exponent” which is easier to understand if you aren't a math geek. Truth is, SQL Server lets you use either a D or an E in scientific notation, thus making for multiple ways to confuse you.
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_NUMBER converts a string to a number of data type NUMERIC. TO_CHAR performs the reverse operation; it converts a number to a string. CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER.
You may try following, but you may loose accuracy:
select cast(cast('1.61022e-016' AS float) as DECIMAL(28, 16))
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
