The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.1k224051
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.6k418576824
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
29.3k287438645
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:

×18
×9
×1

question asked: 29 Dec '12, 23:15

question was seen: 2,621 times

last updated: 03 Jan '13, 06:08