to_char()

Here’s an odd little detail about the to_char() function that happened to show up in a little demonstration code I used to create some data for last Friday’s quiz night.

When generating test data I often rely on doing things with rownum, and one of the thngs I do quite frequently is turn it into a character string. Nowadays I usually use the lpad() function to do this conversion because that lets me specify the defined length of the resulting column. But last Friday, rather than starting from scratch with my standard template script, I created my test case by cloning a script that I’d written in 2003 and the script had used the to_char() function.

So here’s a simple script to create a little table of character data, creating each column in a different way:

create table t as
select
	rownum				id,
	lpad(rownum,5)			lp5,
	rpad(rownum,5)			rp5,
	cast(rownum as varchar2(5))	cvc5,
	cast(rownum as char(5))		cc5,
	to_char(rownum)			tc
from
	all_objects
where
	rownum <= 12
;

And here are the results of a describe followed by a select:

SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 LP5                                    VARCHAR2(5)
 RP5                                    VARCHAR2(5)
 CVC5                                   VARCHAR2(5)
 CC5                                    CHAR(5)
 TC                                     VARCHAR2(40)

SQL> select * from t order by id;

        ID LP5   RP5   CVC5  CC5   TC
---------- ----- ----- ----- ----- ----------------------------------------
         1     1 1     1     1     1
         2     2 2     2     2     2
         3     3 3     3     3     3
         4     4 4     4     4     4
         5     5 5     5     5     5
         6     6 6     6     6     6
         7     7 7     7     7     7
         8     8 8     8     8     8
         9     9 9     9     9     9
        10    10 10    10    10    10
        11    11 11    11    11    11
        12    12 12    12    12    12

12 rows selected.

As you can see, lpad(), rpad() and cast() allow you to specify the column length you want in the table definition. Cast() pads on the right (where necessary) with spaces, as does rpad() – although you can specify a padding character with rpad() and lpad(). Lpad() pads on the left (and it’s the one I use most frequently because it can be convenient to see the character representations sort in numeric order – sometimes I use ’0′ as the padding character).

The “trap” comes from to_char() which results in a column declaration of varchar2(40) but doesn’t do any padding, of course. It’s not much of a trap, really; but when you’ve got into the habit of expecting your character strings to take up some space and see the varchar2(40) in the describe and assume that every string has been padded to 40 characters, it’s easy (and very annoying) to waste time trying to work out why the arithmetic is wrong. It was interesting to note that one of the people offering an answer to the question did exactly what I had originally intended to do, viz: sest up my varchar2(40) to hold 40 characters.

Footnote: it’s crossed my mind that the size used for the column declaration might depend on the NLS settings, and could be affected by the default character set, locale, and use of separators – but I’ll leave that investigation to any readers who have a little spare time.