CODEWITHSUNDEEP
sqlsql-serverazure-sql-databasescientific-notation
Iniyavan
Iniyavan

Reputation: 113

nvarchar to float conversion without scientific notation

I have a column of nvarchar(max). It contains numeric and text records. The numeric/float values are in scientific notation as well. Need to convert the scientific notation to regular float. Example:

create table dbo.float_scientific_test
(
float_varchar_col nvarchar(max)
);

insert into dbo.float_scientific_test
values ('3.0E8'),
        ('5000'),
        ('This is a text.'),
        ('2.157900003E8');

I want to get output like...

float_varchar_col_converted
300000000
5000
This is a text.
215790000.3

I tried in different ways using convert, cast, str(), try_convert. But not getting the expected result. Please help to correct the query. I get the error:

Error converting data type nvarchar to float.

Upvotes: -1

Views: 74

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Just an option using format() and coalesce()... NOT recommended for large sets due to the performance issues of format()

To be clear: The final value is a string.

Example

Select * 
      ,Value = coalesce(format(try_convert(float,[float_varchar_col]),'#.######'),[float_varchar_col])
from float_scientific_test

Results

float_varchar_col   Value
3.0E8               300000000
5000                5000
This is a text.     This is a text.
2.157900003E8       215790000.3

Upvotes: 1

derpirscher
derpirscher

Reputation: 17407

First of all, a column in SQL can only contain values of one type, regardless whether that is a column in a table or in a result set. So your original table does not contain numbers and strings, but string only. And your result set cannot contain numbers and string, but only either all numbers or all strings.

So when you do something like this

 select try_convert(float, float_varchar_col)
 from float_scientific_test

it won't throw an error, but the third row in your result (ie the one containing the text) will be null.

You could then try

 select coalesce(try_convert(float, float_varchar_col), float_varchar_col)
 from float_scientific_test

And that's where your error will start to pop up. Why? Because with TRY_CONVERT(FLOAT, ...) you are defining your result column to be of type FLOAT and thus it can only contain FLOATs or null and the engine will try to convert any value that isn't a FLOAT yet to be a FLOAT but of course that fails for 'This is a text.' and that is what the error is telling you ...

So actually as there is no way, of having mixed types in a column, the only way is to convert everything back to NVARCHAR. This will probably format the FLOATs as you need them. But your result will be all strings and not numbers ... And depending on what you provide as format #.# it may lose decimal places ...

select coalesce(
  format(try_convert(float, float_varchar_col), '#.#'),  --if the value can be converted to float, format it as decimal notation
  float_varchar_col --otherwise try_convert returns null, use the original value of the column
)
from float_scientific_test

Upvotes: 1

Related Questions