How to compare two numbers as strings in MySQL?

Question

I would like to compare two strings as numbers in MySQL. I have the following data in my table:

0,15 kg 0,52 kg 0,68 kg 1,24 kg

Now I would like to compare a string with that data. What I tried is this:

SELECT * FROM `foobar` WHERE weight+0.0 <= '0,7 kg'

Since MySQL seems not to understand the comma as a decimal separator I tried replacing it with a dot:

SELECT * FROM `foobar` WHERE REPLACE(weight+0.0,',','.') <= REPLACE('0,7 kg',',','.')

However I always get weird results including numbers that are larger than 0.7. Oddly when I try ordering the weight column the ordering is correct!

Of course, you should separate it, as Strawberry mentioned. But mysql is able to sort that. For example `where foo > '0,52'` works. — Alex2php, Jan 28 '17 at 18:48
Yes I know it should be stored differently. The sorting doesn't work properly if there is 'kg' behind the number. — Chris, Jan 28 '17 at 18:49
@phpheini Also thought so. But it actually works: http://i.imgur.com/ZNkcdM6.png — Alex2php, Jan 28 '17 at 18:50
use unsigned decimal `5,2` column type to store that data, you can easily append `kg` to it as needed on display — Duane Lortie, Jan 28 '17 at 18:52
@Alex2php try doing `WHERE weight+0.0 <= '1,0 kg'` and you will get 1,24 kg as a result. — Chris, Jan 28 '17 at 18:58
@DuaneLortie I am not able to change the table unfortunately, I need to live with it the way it is :( — Chris, Jan 28 '17 at 18:58

Dmitry Shilyaev · Accepted Answer · 2017-01-29T07:50:02.590

1

Use construct CAST(val AS DECIMAL(10,2))

SELECT * FROM `foobar `
WHERE CAST(REPLACE(weight,',','.') AS DECIMAL(10,2)) <= CAST(REPLACE('0,7 kg',',','.') AS DECIMAL(10,2));

Query was tested and gives correct results.

This solution can't use indexes. Better approach is to query against temporary tables populated with computed data. Or you can add indexed DECIMAL column weight_dec to this table and use trigger to populate data based on weight. Then query using weight_dec.

edited Jan 29 '17 at 07:50

answered Jan 28 '17 at 19:14

Dmitry Shilyaev

713
4
10

Amazing, thank you! Do you know whether those two operations will cost a lot of performance or is it ok to use this in production? – Chris Jan 28 '17 at 19:26
1

I think it is cost some, but you said that you stick with this db design. Don't forget to mark as answer :) – Dmitry Shilyaev Jan 28 '17 at 19:34
1

@phpheini **any** query where a column value is used as an argument to a function or in conjunction with an operaror (e.g. weight + 0) in the `WHERE` clause incurs a severe performance penalty at scale, because it forces a table scan to evaluate the function against every row, preventing the use of any index on the column in question in any RDBMS (not just MySQL). You should never write queries like this. However, if you're doing anything similar to that, then this query is not going to perform any worse than any other. You need to fix your design. Until then, it's the correct solution. – Michael - sqlbot Jan 28 '17 at 23:05
Read up on *sargability* for more on that topic... including [What makes a SQL statement sargable?](http://stackoverflow.com/q/799584/1695906) To be clear, `CAST()` and `REPLACE()` themselves actually have an impact so negligible that it can be disregarded. The problem is that using *any* function in the predicate breaks sargability. – Michael - sqlbot Jan 28 '17 at 23:08
1

If your table is not changes very often, you can create additional table and populate it with computed data from other tables. This is a common practice for reports and stats generation. It gives you the ability to use computed columns in WHERE clause. So you can populate Decimal column and perform quick queries on it. – Dmitry Shilyaev Jan 29 '17 at 07:35

How to compare two numbers as strings in MySQL?

1 Answers1