MySQL Executing string as math operation

Question

In a column I have something like this:

Amount:

12
2x25
192

How is it possible to multiply in this example 2x25 to order it correctly ASC.

My starting point:

SELECT * FROM table
ORDER BY REPLACE(Amount,'x','*') ASC

TIA frgtv10

I think the question is duplicate of this: stackoverflow.com/questions/15100190/… and you may also find this guy here useful: stackoverflow.com/questions/9210865/… — user2299169
– user2299169, Commented May 3, 2013 at 9:50

echo_Me · Accepted Answer · 2013-05-03 10:11:23Z

4

try this

  SELECT 
  CAST(if(Amount LIKE '%x%', SUBSTRING_INDEX(Amount, 'x', 1) *
  SUBSTRING_INDEX(Amount, 'x', -1) , Amount) as  unsigned ) as amount 
  FROM table1
  ORDER BY Amount ASC

DEMO HERE

steps and explaining :

locate fields with x value
sbstring from left and right and multiply it.
then cast the multiplication as unsigned.
order it asc

answered May 3, 2013 at 10:11

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Szocske Over a year ago

makes my answer redundant, and ruins my attempt at education, +1 :-)

Szocske · Accepted Answer · 2013-05-03 09:59:42Z

1

As long as this is the only formula (multiplying 2 numbers), you should be able to hard-code it with INSTR, SUBSTRING, and CONVERT.

answered May 3, 2013 at 9:59

Szocske

7,7092 gold badges22 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL Executing string as math operation

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related