Mysql select numeric price range field without curency

Question

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?

see this solutions [link](http://stackoverflow.com/questions/6278296/extract-numbers-from-a-string) — Samir Nabil, Oct 29 '16 at 10:47
What's the datatype, VarChar? Why? Cast it to a DECIMAL. In Standard SQL it would be: `CAST(SUBSTRING(price FROM 1 FOR POSITION(' ' IN price)) AS DECIMAL(10,2))`. — dnoeth, Oct 29 '16 at 10:48
It would be better to store the currency in a separate column — Paul Spiegel, Oct 29 '16 at 11:50

Mureinik · Answer 1 · 2016-10-29T11:14:51.030

1

You can use substring_index to take only the part before the space. From there on, you just need to cast it to a number and query the minimum and maximum:

SELECT MIN(price), MAX(price)
FROM   (SELECT CAST(SUBSTRING_INDEX(price, ' ', 1) AS DECIMAL(4, 2)) AS price
        FROM   mytable
        WHERE  type = 3) t

edited Oct 29 '16 at 11:14

answered Oct 29 '16 at 10:54

Mureinik

297,002
52
306
350

I get this error: #1064 - 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 'DOUBLE) – paulalexandru Oct 29 '16 at 11:00
I forgot to alias the `cast` expression - edited and fixed. Regardless, that would produce a #1054, not an #1064 error (on my setup, at least). Please try the new query, and if that doesn't work, could you share some info on the mysql version you're using? – Mureinik Oct 29 '16 at 11:03
The mySql version is 5.6.17. Still get the same error – paulalexandru Oct 29 '16 at 11:07
@paulalexandru Huh! apparently MySQL doesn't allow casting to `double`. I'm not sure if this was fixed in 5.7 or in the MariaDB fork, but I was able to reproduce the problem on SQLFiddle's MySQL 5.6. Changing the cast to `decimal` (already edited in) seems to fix it (see [working example](http://sqlfiddle.com/#!9/daff47/2/0)) – Mureinik Oct 29 '16 at 11:17

score 1 · Accepted Answer · answered Oct 29 '16 at 12:19

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).

Mysql select numeric price range field without curency

2 Answers2