Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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?

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

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

asked 02 Oct '12, 06:37

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 02 Oct '12, 06:38

Another solution would be to use REPEAT and RIGHT:

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

... 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.

permanent link

answered 02 Oct '12, 08:24

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

edited 02 Oct '12, 08:27

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 )

permanent link

answered 03 Oct '12, 07:53

Tom%20Mangano's gravatar image

Tom Mangano
accept rate: 8%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • 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:


question asked: 02 Oct '12, 06:37

question was seen: 4,261 times

last updated: 03 Oct '12, 15:30