CODEWITHSUNDEEP
mysqlcastingdecimal
Fabian Feriks
Fabian Feriks

Reputation: 67

convert string to decimal or float mysql

i have the next problem, i have a table with a string column, but i need convert this column in decimal or float. I tried with cast and convert but doesn't work example

input

3.50
7.10
18.50
27.00
46.50
46.90
117.90
226.70
274.70
Not available
Not available

tried

select cast(price as decimal(16,2)) from products

output

0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00

i see this warnings

Warning: #1918 Encountered illegal value '' when converting to DECIMAL

Warning: # 1292 Wrong truncated DECIMAL value:

I need later multiplier this column with another column

Upvotes: 2

Views: 4790

Answers (2)

AndreiXwe
AndreiXwe

Reputation: 763

Try

select convert(price, decimal(16,2)) from products

Upvotes: 2

nbk
nbk

Reputation: 49373

If you add simple a 0 to the value you get a float or decimal which you can cast or usde directly everythin that is not a number is cobverteed to 0, but you can Replace Not available with another number

CREATE TABLE textvalue
    (`Input` varchar(13))
;
    
INSERT INTO textvalue
    (`Input`)
VALUES
    ('3.50'),
    ('7.10'),
    ('18.50'),
    ('27.00'),
    ('46.50'),
    ('46.90'),
    ('117.90'),
    ('226.70'),
    ('274.70'),
    ('Not available'),
    ('Not available')
;

SELECT CAST(Input + 0 AS DECIMAL(12,2))FROM textvalue

| CAST(Input + 0 AS DECIMAL(12,2)) |
| -------------------------------: |
|                             3.50 |
|                             7.10 |
|                            18.50 |
|                            27.00 |
|                            46.50 |
|                            46.90 |
|                           117.90 |
|                           226.70 |
|                           274.70 |
|                             0.00 |
|                             0.00 |

db<>fiddle here

Upvotes: 3

Related Questions