How to convert nvarchar(254) to decimal(7,2)

Question

I have a table with 10M+ rows and want to change the data type of one the columns from nvarchar(254) to decimal(7,2). What is the most efficient and effective query to make this change?

I have tried using ALTER in order to make this change, but get an error in SSMS

Error converting data type nvarchar to numeric.

I have also tried using CAST, but this results in errors as well. Admittedly, I'm not a DBA so I have struggled to understand the following:

1. How to properly write a CAST query that does not yield errors

2. Whether the CAST and CONVERT functions change the design of the data at the database level (meaning in the Object Explorer, when I right-click the table and then click 'Design' I see the data type of the column has changed) or if the changes only last until the next query is run or the program is exited.

This table was initially created over a month ago as the result of a workflow that was run a few months ago; this workflow has since been scheduled to push new data to the table on an hourly cadence, so deleting the job/table and starting over is not an option.

SET STATISTICS TIME ON

ALTER TABLE Clone3

ALTER COLUMN Price decimal(7,2)

The ultimate goal is to store this data correctly so that arithmetic operations can be performed when it is ingested into other visualization programs (e.g., Tableau, Power BI, etc.) That said, the expected result here is for the data type to be changed to Decimal(7,2) but the actual result is nvarchar(254).

UPDATE

After running SELECT Price from Clone3 WHERE TRY_CONVERT(decimal(7,2),Price) IS NULLthere are 239 records that return in scientific notation. For example -5.0000000000000003E-2

FINAL UPDATE

I ran the following query to update the records that were causing the conversion error (these were negative numbers like '-0.05' being converted to scientific notation for some strange reason).

UPDATE Clone3 SET Price = CAST(Price AS Float) WHERE TRY_CONVERT(decimal(7,2), Price) IS NULL

Because all of the records are now in a numeric data type, I can convert the entire dataset to decimal(7,2), using this query.

ALTER TABLE Clone3 ALTER COLUMN Price decimal(7,2)

I think I can call this solved, so many thanks to everyone for their responses, especially @Larnu for the code snippet that eventually helped me figure this out.

Answer 1

This 5.9999999999999998E-2 cannot be converted directly to decimal(7,2), although it can be converted to a float, which can then be converted to a decimal(7,2). EG

select cast(cast('5.9999999999999998E-2' as float) as decimal(7,2))

While not the most efficient, or a general solution for this kind of thing, you could alter the table twice, eg:

use tempdb

drop table if exists t
create table t(s varchar(200))
insert into t(s) values ('5.9999999999999998E-2')

go

alter table t alter column s float
alter table t alter column s decimal(7,2)

go
select * from t

Answer 2

The most efficient way is probably to empty the table and reload it:

select *
into temp_t
from t;

truncate table temp_t;

alter table t alter column price decimal(7, 2);

insert into t
    select *
    from temp_t;

There is more overhead to updating the records in place.