paulalexandru paulalexandru - 1 month ago 5
MySQL Question

Mysql select numeric price range field without curency

I have a table which looks like this:

type | price
------+-----------
1 | 0.99 $
------+-----------
2 | 1.22 €
------+-----------
2 | 2.99 €
------+-----------
3 | 8.00 CHF
------+-----------
1 | 1.99 $
------+-----------
3 | 2.00 CHF
------+-----------
3 | 2.50 CHF


As you can see it contains prices along with the currency and the type which is bound with the currency.

I want to do something like this:

SELECT "range" FROM my_table where type = 3

// result = 2.00 - 8.00


So the currency should be removed and kept only the price. Is there any way to do this?

Answer

Mureinik's answer is fine. I want to note that you can also simply do:

SELECT MIN(price + 0), MAX(price + 0)
FROM t
WHERE type = 3;

This uses MySQL's rules for implicit conversion. It will convert the leading characters of a string to a number (integer or decimal). If strings start with a non-digit character, the value is 0 (as opposed to an error).

Comments