One of the things I need to do frequently in SQL reporting is to format decimals as a string usually in the Format 9,999.99 with or without a currency symbol. I dont see any easy way to do this in SQL ANywhere.

Has anyone developed some easy short cuts to do this in SQL Anywhere?

asked 29 Dec '12, 23:15

Glenn%20Barber's gravatar image

Glenn Barber
1.1k234051
accept rate: 0%

edited 29 Dec '12, 23:15


SQL Anywhere needs, but does not have, a "NUMBERFORMAT" function similar in intent to DATEFORMAT.

Traditionalists say that formatting belongs on the client side, not the server. With the introduction of the built-in HTTP server, however, that argument is losing strength... if a SQL Anywhere service can produce a fully-formed HTML page to be sent straight to the browser, it surely should be able to format a nice dollar amount string like '$1,234,567.89'.

In the meantime, if you need it, you have to write it yourself, as a CREATE FUNCTION f$ ( input_number VARCHAR ( 100 ) ) RETURNS VARCHAR ( 200 ). If you pass it a numeric data type, it will be automatically converted to VARCHAR ( 100 ) which takes care of the decimal point, and then you code does the rest: fix the number of decimal digits at 2, insert commas, append a $. The devil will be in the details (out of bounds numbers, etc).

So no, no easy shortcuts.

permanent link

answered 30 Dec '12, 09:33

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

HI Breck

Please note my comment below to Jeff - as you can see we often need different formats for separate columns in a single transaction. I don't think I could accommodate this with a NUMBERFORMAT. I suggested an extention to the CONVERT - but even the way CONVERT was implemented is less flexible than a simple Masking Function where we can provide the desired format as a string (e.g. Convert(mydecimalcol, '#.##9,99') ) - note the British use of comma's and decimals.

The problem is that my need for this is current so I will need to challenge myself with the creation of a custom function - and was hoping that someone had already done this one.

(31 Dec '12, 13:36) Glenn Barber
Replies hidden
Comment Text Removed
1

Post your requirements as a new question, with lots and lots of examples of the input and output.

Don't know why you don't like the idea of a DATEFORMAT-like NUMBERFORMAT function, since DATEFORMAT does have a very flexible "picture" clause whereas CONVERT has a highly-restricted "code".

(31 Dec '12, 16:51) Breck Carter

I have added a new enhancement request (CR #728186) to address this area of the product to be possibly addressed in a future version of SQL Anywhere.

From my perspective, we could either enhance the database server to add more specific string processing functions (as Breck suggested - something like NUMBERFORMAT / CURRENCYFORMAT) for the formatting use-cases currently missing or potentially investigate a more 'universal' way of performing string formatting in the database server.

Thank you for the enhancement request.

permanent link

answered 31 Dec '12, 13:02

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

1

Hi Jeff

When we do financial processing for the Film and TV industry, transactions often come in three different currencies - the source (payment currency), the bank deposit currency, and the home currency. Each of these can be in a different currency and have a different currency format. For that reason I would like to see something where we can specify the display mask for each separate column rather than a general numberformat / currency format. I would like to perhaps see this done as an extention of the CONVERT function which we use to regionalize date formats.

Thanks

Glenn

(31 Dec '12, 13:22) Glenn Barber
Replies hidden
Comment Text Removed
1

I would agree that a display mask approach would be the most flexible since then one could use whatever logic was required to generate / select the appropriate mask - which might well be data dependent.

(03 Jan '13, 06:08) Justin Willey

I'd add that not only HTTP responses will profit from a server-side number/currency formatting - server-side reports do, too.

FWIW, this issue has been discussed here in several places, and sample code is available, as well. You may just search with a tag like "formatting"...

permanent link

answered 30 Dec '12, 12:00

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

4

It's too bad there isn't a place where folks could easily collaborate on code like this.

It could have a catchy name, say, like "Code Exchange" :)

(30 Dec '12, 17:42) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×19
×9
×1

question asked: 29 Dec '12, 23:15

question was seen: 3,075 times

last updated: 03 Jan '13, 06:08