How do we do formatting of decimals as currency strings in SQL

[glenn_barber]

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?

[jeff_albion]

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.

[glenn_barber]

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

[justin_willey]

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.

[Breck_Carter]

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.

[glenn_barber]

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.

[Breck_Carter]

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

[VolkerBarth]

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

[Breck_Carter]

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" 🙂