Negative, Captain

Seen in the wild:

... WHERE substr(amount,0, 1) != '-'

If you wanted to query a table of monetary transactions for any refunds (i.e. where the transaction amount is negative), how would you do it? Perhaps you’d think about avoiding problems that might occur if the default number format were to change, hm?

(before you say it: no, there is no index on amount, so it wasn’t a misguided attempt to avoid an index access path…)

I’m off to InSync10
Forms Library: PKG_ITEM


  1. Sad to say, but my instinct is to ask whether they are storing the amount as a character column.

    I do keep trying to tell developers that, in the real world, we start counting from 1, not 0. Sometimes it helps to actually show them on their fingers.

    • Nope, definitely is a NUMBER 🙂

      I see you spotted the benign zero there, too.

      What surprised me a bit is that they were sophisticated enough to use the != operator instead of <>, which is more common with newbies.

Leave a Reply

Your email address will not be published / Required fields are marked *