Convert decimal to string and select all ending with .99

user_898042 0
Viewed 144

I have a table 'book' with 2 columns: title and price:

title | price
--------------
'abc' | 567.99
'cdl' | 100.40
'ttt' | 300.99
'uio' | 200.90

How would I select all prices ending in .99? I have tried this so far:

select * from book where substring(price, 0, substring_index(length(price))) like '%.99'
select mysql
2 Answers

You don't need to do anything convert it to a string. Using a number in a string context does that already.

mysql> select 4.99 like '%.99';
+------------------+
| 4.99 like '%.99' |
+------------------+
|                1 |
+------------------+

Or even

mysql> select reverse(4.99) like '99.%';
+---------------------------+
| reverse(4.99) like '99.%' |
+---------------------------+
|                         1 |
+---------------------------+
Bill_Karwin_208600

If you wanted to try this without doing the string conversion, I would recommend that you just run:

SELECT * FROM book WHERE MOD(price , 1) = 0.99

Since it will return the part after the decimal and check it numerically. This should be the faster option.

Otherwise you could to do a string conversion implicitly through the "Like" operator and just check the end, though this will likely be slower since there is an extra operation taking place:

SELECT * FROM book WHERE CAST(price as CHAR) LIKE "%.99"
user_64958890
Related