Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: Convert varchar to decimal (with considering exponential notation as well)

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?

like image 653
Tilak Avatar asked Aug 26 '13 20:08

Tilak


People also ask

Can you convert varchar to decimal in SQL?

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.

Can you use scientific notation in SQL?

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.

How do you convert a whole number to a decimal in SQL?

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).

What is To_number in SQL Server?

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.


1 Answers

You may try following, but you may loose accuracy:

select cast(cast('1.61022e-016' AS float) as DECIMAL(28, 16))
like image 68
i-one Avatar answered Sep 28 '22 08:09

i-one