MYSQL case statment comparing string values incorrect outcome-CodePudding

I have the following mysql query with what should be a simple case statement in comparing two srting values and populating whichever is the greater value as follows:

SELECT
wca.bond.secid,
wca.bond.IssueAmount,
wca.bochg.NewOutValue,
case when wca.bond.IssueAmount >= wca.bochg.NewOutValue
     then wca.bond.IssueAmount ELSE wca.bochg.NewOutValue END AS IssueAmount2
from
wca.bond
INNER JOIN wca.bochg ON wca.bond.secid = wca.bochg.SecID
WHERE
bond.secid = 5955156;

The following output is produced:

As you can see from the output the case statment does snot seem to work as it should as I would expect to see 12200 as the value under IssueAmount2 as opposed to 7200. I thought it may be due to the fields being compared being different formats but I can confirm they are both Varchars having the same length 20.

Does anyone know why I'm having this issue?

CodePudding user response：

In VARCHAR() datatype, it will compare the first character. If the first character is the same, it will compare the second character and continue the comparison until it found a difference. In your case, although it's obvious that 12200 is more than 7200, that's only true if we're comparing numbers. So, with varchar, this is what happen:

[7]200 = 7 is bigger than 1
[1]2200 = 1 is smaller than 7

So what you need is to convert the value into integer. Here's a suggestion to your current query:

SELECT
wca.bond.secid,
wca.bond.IssueAmount,
wca.bochg.NewOutValue,
case when wca.bond.IssueAmount 0 >= wca.bochg.NewOutValue 0
     then wca.bond.IssueAmount ELSE wca.bochg.NewOutValue END AS IssueAmount2
from
wca.bond
INNER JOIN wca.bochg ON wca.bond.secid = wca.bochg.SecID
WHERE
bond.secid = 5955156;

Add 0 to the column in comparison to quickly make the column value identified as integer. You can also use CONVERT() or CAST():

CONVERT(varchar_val1, SIGNED) >= CONVERT(varchar_val2, SIGNED)
...
or
CAST(varchar_val1 AS SIGNED) >= CAST(varchar_val2 AS SIGNED)
...

Demo fiddle