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?
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.
answered 30 Dec '12, 09:33
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
Thank you for the enhancement request.
answered 31 Dec '12, 13:02
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"...
answered 30 Dec '12, 12:00