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.

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.1k274456
accept rate: 8%

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
32.5k5417271050
accept rate: 20%

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.8k171175
accept rate: 25%

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
40.5k365556827
accept rate: 34%

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:

×22
×10
×1

question asked: 29 Dec '12, 23:15

question was seen: 7,761 times

last updated: 03 Jan '13, 06:08