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

2 thoughts on “Negative, Captain

  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.


    1. 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

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s