My SQL casting an comparing in one query?

Question

The SQL query:

SELECT badgename FROM badges WHERE stock < desiredstock

Carried out on this table:

Should Yield

Summit Everest, Elderly Management Badge

Yet it yields nothing, and no error is produced.

I hypothisie that since both stock and desiredstock are columns with VARCHARS, the Query cannot be carried out as you can't compare two strings mathematically.

So is it possible to rewrite the query so both stock and desiredstock values are casted as VARINTS?

yes. try one of the solutions here https://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query — Garr Godfrey, Jan 28 '19 at 10:02
Possible duplicate of [Convert text into number in MySQL query](https://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query) — Garr Godfrey, Jan 28 '19 at 10:03

score 0 · Answer 1 · answered Jan 28 '19 at 10:10

0

Here is the soluuon. Thanks for the link to answer in comments.

SELECT badgename FROM badges WHERE CAST(stock AS UNSIGNED) < CAST(desiredstock AS UNSIGNED)

answered Jan 28 '19 at 10:10

Lyra Orwell

1,048
4
17
46

My SQL casting an comparing in one query?

1 Answers1