Skip to content

Negative, Captain

July 22, 2010

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…)

From → SQL

  1. Gary permalink

    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.

Comments are closed.


Get every new post delivered to your Inbox.

Join 271 other followers

%d bloggers like this: