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.) asked 02 Oct '12, 06:37 Volker Barth 31.6k●321●465●678 accept rate: 32%

 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. answered 02 Oct '12, 08:24 Mark Culp 23.4k●9●133●275 accept rate: 41% 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... (02 Oct '12, 08:45) Volker Barth Replies hidden 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. (02 Oct '12, 08:53) Mark Culp Yes, I agree with your sound reasoning. (And yes, the question surely arose from the recursive CTE sample...) (02 Oct '12, 09:03) Volker Barth
 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 Tom Mangano 632●22●26●36 accept rate: 6% 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... (03 Oct '12, 15:30) Volker Barth
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×415
×9
×5

question asked: 02 Oct '12, 06:37

question was seen: 1,626 times

last updated: 03 Oct '12, 15:30