CODEWITHSUNDEEP
mysqlstringcastingdecimal
zuckerbrot
zuckerbrot

Reputation: 117

Casting Scientific notation from Varchar to Decimal in mysql

On a mySQL database, in a dataset all values are stored as varchar, show amounts of Euro and look like this: 'EUR 2.5E+2', 'EUR 56.95', 'EUR 1E+2'

Now, I want to make it useful again and convert this into decimal for further calculation, but can't figure out how to do it.

I have tried to follow the suggested solution to this question: Casting Scientific Notation (from varchar -> numeric) in a view

However, I failed to replicate the solution as a query, view or stored procedure, much less adapt it to my problem.

I really hope someone can help me out here. Thanks in advance!

Upvotes: 2

Views: 2995

Answers (2)

zuckerbrot
zuckerbrot

Reputation: 117

Thank you very much Rick James, your solution works quite well. However, I prefer to explicitly cast the substring as decimal:

CAST(SUBSTRING_INDEX(p.amount, ' ', -1) AS DECIMAL(5,2))

Now I can carry out calculations on the result (see comment below Rick's answer).

Upvotes: 3

Rick James
Rick James

Reputation: 142433

SELECT SUBSTRING_INDEX('EUR 2.5E+2', ' ', -1) + 0;

->

250

Caution: This assumes there is one space, and the numeric value is after the space.

Upvotes: 1

Related Questions