How do I format non-negative integers to add leading zeroes?

[VolkerBarth]

Yes, I know, general number formatting is not (yet?) supported as a builtin feature in SQL Anywhere.

So, for the pretty easy requirement to format integers > 0 to contain a minimum of nDigitCnt digits (filled with spaces or leading zeroes), is there a better way than using REPEAT() and LENGTH(), such as the following sample?

BEGIN
   DECLARE nDigitCnt INT = 5;
   DECLARE chPlaceholder CHAR = '0';
   DECLARE nNr INTEGER = 23;    
   SELECT REPEAT(chPlaceholder, nDigitCnt - LENGTH(nNr)) || nNr;
END;

(As the sample shows, numbers having more than n digits should be formatted as-is.)

[MarkCulp · Accepted solution]

Another solution would be to use REPEAT and RIGHT:

BEGIN
   DECLARE nDigitCnt INT = 5;
   DECLARE chPlaceholder CHAR = '0';
   DECLARE nNr INTEGER = 23;    
   SELECT RIGHT( REPEAT( chPlaceholder, nDigitCnt ) || nNr, nDigitCnt );
END;

... but the difference is likely negligible in terms of performance.

The drawback to this solution (or "feature") is that the output is always exactly nDigitCnt characters. I.e. numbers that require more than nDigitCnt characters will be left truncated ... which may or may not be desirable.

[VolkerBarth · Accepted solution]

As the sample shows, numbers having more than n digits should be formatted as-is.

So I would see this as a drawback here.

On the other hand, your solution will evaluate nNr only once, making it possible more usable in case that number is specified as a complex expression...

[MarkCulp · Accepted solution]

Yes but in cases when you know there is a reasonable upper bound on the length of nNr (as in your recent question about recursive queries) then truncation is unlikely and not an issue.

Also, in some cases you want to guarentee column widths (e.g. writing reports) and in these cases truncating the number (and/or filling in with stars ('*') or some other out-of-band character) is desirable. Again, in such cases knowing and using an upper bound value for nDigitCnt makes the truncation not an issue.

[VolkerBarth · Accepted solution]

Yes, I agree with your sound reasoning. (And yes, the question surely arose from the recursive CTE sample...)

[t1950]

assuming columnName is the int column in the table, and 9 is the desired length of the string,

put this in a case statement in the select ...

right( '0000000000' + convert( char( 10 ), columnValue ), 9 )

[VolkerBarth]

This is a variation of Mark's solution, methinks, and would be fine for padding to a small and already known number of digits.

Note that you can substitute the

+ convert( char( 10 ), columnValue )

simply with

|| columnValue

since the string concatenation operator || will cast impliticly...