I am reviewing some code and came across: RPAD(LPAD(TO_CHAR(col), 9, '0'), 11, '0') where col is NUMBER(5, 0) NOT NULL DEFAULT 0.
FWIW (in QA): SELECT LENGTH(col) L, SIGN(col) S, COUNT(*) C FROM tab GROUP BY LENGTH(col), SIGN(col) ORDER BY 1,2;
1 0 22196
1 1 5746
2 1 53553
3 1 410260
4 -1 16
4 1 23198
5 1 25
The sample output i have contains no negative numbers. Seemingly, it is excluded by the query with other logic (though not directly) or this data is only in test cases.
The intention seems to be to add up to 8 leading zeroes and 2 trailing zeroes. One of the pad functions seems redundant as TO_CHAR itself can do the work, although it does not seem that TO_CHAR can add both leading and trailing zeroes in the same call. Since the latter is only two zeroes, i figure simply concatenating them is better: TO_CHAR(col, 'FM000000009') || '00'. Technically, this is not the same as the current expression, as where the number is negative, the sign is placed in the middle of the string! I assume this not intended as the case is not expected. Perhaps i can "fix" it and leave a comment explaining the original behavior.
Is there a way to add both preceding and trailing zeroes with one function?
Message was edited by: Brian Tkatch
Put the parenthesis in the correct place. Oops!