ORDER BY for currency values

Question

I have a prize_value column in one of the MySQL tables and now I need to do sorting based on this. This column is actually VARCHAR and has currency symbols attached with it. But not for all the values. The currency symbol can be USD(dollar sign), POUND or INR (Rupee Symbol). So currently the order by is not working properly. How can I fix this without removing the currency symbol manually ?

Here are some sample values in the column:

.50
£10
£100
$15
$20
£25
£50
10
₹30

Can you show a sample data for your table column? – Rahul Tripathi Aug 27 '15 at 09:00 — Rahul Tripathi, Aug 27 '15 at 09:00
@RahulTripathi edited the question – Happy Coder Aug 27 '15 at 09:06 — Happy Coder, Aug 27 '15 at 09:06

score 2 · Accepted Answer · answered Aug 27 '15 at 09:13

2

You need two columns:

one for the value, a float/double (or an integer) that can be ordered, used in operations such as sums, etc.
one for the currency (a char(3) following ISO 4217 standard) used for other purposes (display, maybe conversion, etc.)

Storing both value and symbol inside a varchar is non-sense, especially since the position of the currency symbol varies from country to country (can be at the beginning or at the end), there might be a space between the symbol and the value, or not, etc.

That being said, if you don't want to change your table, something like that could work:

ORDER BY CAST(
  REPLACE(REPLACE(REPLACE(thecolumn,'$',''),'£',''),'₹','')
  AS DECIMAL(10,2)
)

answered Aug 27 '15 at 09:13

rlanvin

6,057
2
18
24

Yeah. Actually this has been done by someone else. I have modified it to another correctly formatted table. But I need this for some reporting purpose. – Happy Coder Aug 27 '15 at 09:14
@HappyCoder What about the second part of my answer? – rlanvin Aug 27 '15 at 09:22
Yes, I Am trying with that only :) – Happy Coder Aug 27 '15 at 09:27

McFly · Answer 2 · 2015-08-27T11:27:37.763

This worked for me...

I created a table with about 15 rows, some containing characters ($,£,€) and some not. I created a calculated field CAST as DECIMAL, then I check the leftmost character to see if it can be cast as a number, if so we check the rightmost character since some currencies put the symbol at the end. If the right character is > 0, we use prize_value, otherwise we take the left characters. Back to the first IF statement, if it wasn't greater than 0 that means it either leads with a 0 (which doesn't matter) or it was a non-number (in this case a monetary symbol), so we take the rightmost text. Finally we close off the CAST field and sort by it. You can put this as a calculated field, or as the ORDER BY. I put it both ways below.

SELECT prize_value,
      CAST(IF(LEFT(test.prize_value,1) IN ('.',','),
             REPLACE(test.prize_value,LEFT(test.prize_value,1),'0.'),
             IF(CAST(LEFT(test.prize_value, 1) AS DECIMAL(10,2)) > 0,
                IF(CAST(RIGHT(test.prize_value, 1) AS DECIMAL(10,2)) > 0,
                  test.prize_value,
                  LEFT(test.prize_value, LENGTH(test.prize_value) - 1)
                ),
                RIGHT(test.prize_value, LENGTH(test.prize_value) - 1)
             )
           )
      AS DECIMAL(10,2)) AS prize_value_only
  FROM chatter.test
ORDER BY prize_value_only ASC

--- OR ---

SELECT prize_value
  FROM chatter.test
ORDER BY 
      CAST(IF(LEFT(test.prize_value,1) IN ('.',','),
             REPLACE(test.prize_value,LEFT(test.prize_value,1),'0.'),
             IF(CAST(LEFT(test.prize_value, 1) AS DECIMAL(10,2)) > 0,
                IF(CAST(RIGHT(test.prize_value, 1) AS DECIMAL(10,2)) > 0,
                  test.prize_value,
                  LEFT(test.prize_value, LENGTH(test.prize_value) - 1)
                ),
                RIGHT(test.prize_value, LENGTH(test.prize_value) - 1)
             )
           ) AS DECIMAL(10,2)) ASC

ORDER BY for currency values

2 Answers2

Linked