convert string to decimal or float mysql

Question

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

Just add 0; not cast or convert is needed. Even adding 0 may not be necessary if you use the string in a numeric context. Do you also need TRIM()? — Rick James
– Rick James, Commented Aug 22, 2020 at 5:03

nbk · Accepted Answer · 2020-08-22 01:02:07Z

3

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

answered Aug 22, 2020 at 1:02

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Terry Carmen Over a year ago

This was the only suggestion that worked when the column contained some empty/null/blank rows!

AndreiXwe · Accepted Answer · 2020-08-22 01:03:15Z

2

Try

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

answered Aug 22, 2020 at 1:03

AndreiXwe

7634 silver badges20 bronze badges

Comments

Rick James · Accepted Answer · 2025-02-17 21:50:13Z

0

Collectives™ on Stack Overflow

convert string to decimal or float mysql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related