RPAD related Query 2

[pnad]

Hi,

I need to count the number of checks issued for a given day. This needs to go in a fixed length flat file and be right justified zero filled across 10 places.

When I use
rpad(to_char(count(a.check_number),'0000000000')||' ',11)

Then I get an additional space in the front( 0000000005). If I use

rpad(to_char(count(a.check_number),'0000000000')||' ',10) then the count at the end goes away( 0000000000).

If I use to_char(count(a.check_number),'0000000000' then I get ( 0000000005) - still an additional space in the front.

How do I format this to get (0000000005).

Please help.

Thanks.

 

[SantaMufasa]

PNAD, I believe what you should use is the LPAD function (i.e., pad on the left with zeros; not RPAD...pad on the right with something). Here is an example of an 11-digit, left-zero-filled result:

SQL> select lpad(count(*),11,0) from user_tables;

LPAD(COUNT(
-----------
00000000440

Let us know if this is what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

As Dave says, LPADding the number is the way to go.

For future reference, though, the reason you were getting an extra space before the zeroes is that, in the TO_CHAR, Oracle reserves a space for a - sign in case the number is negative. Look:

SELECT '>'||TO_CHAR(123,'0000000000')||'<' test
FROM dual
/
TEST
-------------
> 0000000123<

You can fix it like this:

SELECT '>'||TO_CHAR(123,'fm0000000000')||'<' test
FROM dual
/
TEST
-------------
>0000000123<

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[pnad]

Thank you. I used LPAD and it works now.