Compare strings as numbers

Question

I have some prices in a MySQL db, written like:

How can I compare these as numbers, so I can use MIN (price) correctly in my SELECT queries?

I have found MySql: Compare 2 strings which are numbers?, but it looks like I can't nest the MIN and the CAST correctly.

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

You can find the link helpful

Try this sql fiddle!

or try this:

SELECT min(CONVERT(price,UNSIGNED INTEGER)) AS num
FROM sample

edited May 23 '17 at 11:49

Community

answered Nov 07 '14 at 14:39

xtras

score 0 · Answer 2 · answered Nov 07 '14 at 14:26

0

You can use a cast expression inside min:

SELECT MIN (CAST mycolumn AS signed)
FROM   my_table

answered Nov 07 '14 at 14:26

Mureinik

Thanks, but that's exactly what I have tried. And then I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'price AS signed), image FROM masterproducts m, shopproducts s' at line 1 Price field is a varchar Query: SELECT name, MIN(price AS SIGNED), image FROM masterproducts m, shopproducts s WHERE m.name = s.mastername – Louisa Nov 07 '14 at 14:36
You're missing the `cast` key-word before `price`. – Mureinik Nov 07 '14 at 14:40
Didn't help (the CAST keyword was there, it's just me who missed it when typing things here...) – Louisa Nov 07 '14 at 14:45

2 Answers2