Leading Zeros…the bleeding edge of TO_CHAR format masks

We’ve been busy playing with our new house.
Deb has re-arranged the furniture about three times since we moved in.
Well, she’s actually just specified where she wants the furniture, it’s me who has actually moved it about.
In fairness, she is the one who brings all the taste, style and interior design skills to our relationship. I’m more of the Igor who just does the legwork at her bidding.
My protestations about the importance of Euro 2012 have been met with the response that it can’t be that important a football tournament because Wales aren’t playing in it.
One of the benefits of moving into a new place is that stuff just works. You hit a light switch and the light comes on.
It’s a bit like using TO_CHAR to convert a number in Oracle really. Look, I’ll show you…

CREATE TABLE my_numbers(
    some_num VARCHAR2(2));
    
INSERT INTO my_numbers VALUES('01');
INSERT INTO my_numbers VALUES('09');
INSERT INTO my_numbers VALUES('10');

Yes, if you are going to store numeric values in a table then defining the column as NUMBER ( or variation therof) is a bit more sensible than using VARCHAR2. However, when you’re faced with those oh so useful but oh so overused lookup tables that are used to store everything from days of the month to printer names, things get a little less straightforward.

Now, if we want to see if a number exists in the table ….

SQL> SELECT some_num
  2  FROM my_numbers
  3  WHERE some_num = TO_CHAR(10);

SO
--
10

SQL>

Notice that we’re doing the conversion on the value we’re passing in so that we don’t disable the use of any Index that might be on the column.

So far, so good. Let’s try it again…

SQL> SELECT some_num
  2  FROM my_numbers
  3  WHERE some_num = TO_CHAR(1);

no rows selected

SQL>

Hmmm…no rows. Really ? Let’s try again with a format specifier…

SQL> SELECT some_num
  2  FROM my_numbers
  3  WHERE some_num = TO_CHAR(1, '00');

no rows selected

SQL>

Still nothing. Maybe we should check the bulb ?

SQL> SELECT TO_CHAR(1, '00') FROM dual;

TO_
---
 01

SQL>

Yep, the return value is what we would expect. It’s exactly the same as the value in the table…or is it ?

SQL> SELECT LENGTH( TO_CHAR(1, '00')) FROM dual;

LENGTH(TO_CHAR(1,'00'))
-----------------------
                      3

SQL>

So, obviously not the same then. The reason that the conversion returns a value with a length of three is that the format allows a leading space for a minus sign.
Fortunately, we have the FM format specifier, which strips any leading or trailing spaces…

SQL> SELECT LENGTH( TO_CHAR(1, 'FM00')) FROM dual;

LENGTH(TO_CHAR(1,'FM00'))
-------------------------
                        2

SQL>

If we now plug this into our original query….

SQL> SELECT some_num
  2  FROM my_numbers
  3  WHERE some_num = TO_CHAR(1, 'FM00');

SO
--
01

SQL>

Just to round things off, do we get the same problem with TO_NUMBER ?

SQL> SELECT some_num
  2  FROM my_numbers
  3  WHERE TO_NUMBER(some_num) = 1
  4  /

SO
--
01

SQL>

So there you have it. TO_CHAR is just a little bit quirky.
Deb is looking thoughtfully at that Feng Shui website again. I think I may be back on Igor duty before much longer.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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