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.)
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.
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 )
answered 03 Oct '12, 07:53