SQL Server FAQ-convert the numeric string of scientific notation to the decimal type

When operating the database today, You need to convert the string to the decimal type. The Code is as follows:

 

Select cast ('0. 12' as decimal (18, 2 ));

Select convert (decimal (18, 2), '0. 12 ');

 

When you need to convert the numeric string of the scientific notation into a decimal, the following two writing methods are reported:

MSG 8114, level 16, state 5, line 1

Error converting data type varchar to numeric.

 

Select cast ('0. 12e + 006 'As decimal (18, 2 ));

Select convert (decimal (18, 2), '0. 12e + 006 ');

 

I found a lot of information online and did not find the answer. Finally, we accidentally discovered that when the float type is converted to a string, a numeric string with scientific notation will be generated:

Select cast (1234400000 as float) as varchar)

1.2344e + 009

 

In reverse thinking, the numeric string of scientific notation can be converted to the float type, and then to the decimal type.

Select cast ('1. 2344e + 009' as float) as decimal ))

 

 

Summary: if it is a numeric string of scientific notation, it can be first converted to float and then converted to another numeric type.

When operating the database today, You need to convert the string to the decimal type. The Code is as follows:

 

Select cast ('0. 12' as decimal (18, 2 ));

Select convert (decimal (18, 2), '0. 12 ');

 

When you need to convert the numeric string of the scientific notation into a decimal, the following two writing methods are reported:

MSG 8114, level 16, state 5, line 1

Error converting data type varchar to numeric.

 

Select cast ('0. 12e + 006 'As decimal (18, 2 ));

Select convert (decimal (18, 2), '0. 12e + 006 ');

 

I found a lot of information online and did not find the answer. Finally, we accidentally discovered that when the float type is converted to a string, a numeric string with scientific notation will be generated:

Select cast (1234400000 as float) as varchar)

1.2344e + 009

 

In reverse thinking, the numeric string of scientific notation can be converted to the float type, and then to the decimal type.

Select cast ('1. 2344e + 009' as float) as decimal ))

 

 

Summary: if it is a numeric string of scientific notation, it can be first converted to float and then converted to another numeric type.