CODEWITHSUNDEEP
sqlsql-serversql-insert
CandleWax
CandleWax

Reputation: 2219

How to cast varchar value to decimal?

I have a column that is currently set as nvarchar(max) and I want to convert the column to decimal(38,2). The problem I am running into is that a few of the millions of rows are in the format of -1.943E12 and because the E is present I get an error, Cannot convert nvarchar into numeric, when I try to cast the column into a decimal. Is there anyway to do this?

CREATE TABLE practice (cost nvarchar(max))
INSERT INTO practice values ('123'),('44232.99'),('43.4432'),('1.943E12')

SELECT CAST(cost as decimal(32,2)) FROM practice

What I'm ultimately trying to do is insert data from a staging table (where all columns are nvarchar(max)) into a table with appropriate datatypes. I kept getting an error, so after digging through each of the 40 columns, I found 5 columns where this scientific notation occurs. Any advice on how to do this at scale without having to check each column?

INSERT INTO practice_corrected_datatypes
SELECT * FROM practice

Upvotes: 2

Views: 1523

Answers (3)

Cham
Cham

Reputation: 113

Try this...

update p set cost = cast(cast(cost as float) as decimal(32,2)) from practice p where cost like '%E%'

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35593

assuming you have try_parse() and try_cast()

SELECT try_cast(try_parse(cost as float) as decimal(32,2))
FROM practice

The advantage is that they return NULL instead of failing. You could probably get by with just the inner try_parse() 

SELECT cast(try_parse(cost as float) as decimal(32,2))
FROM practice

Upvotes: 3

Michael
Michael

Reputation: 9044

Numeric cannot convert from exponential format, but float can. Therefore you could do this by going via float. For example:

print cast(cast('1.943E12' as float) as decimal(38,2))

Upvotes: 3

Related Questions