We have HANA database with SPS 12.
We have requirement where we need to format decimal as per business rule -
Typically I want comma as thousand separator as -
Input - 12345678.1234
Ouput - 1,23,45,678.12
In Oracle, it is possible with to_char(input,'99,99,99,999.99').
Is it possible to achieve same in HANA?
What you're asking sounds as if the format shall be used for client presentation of the number values. If that is the case, please use the data formatting options of the client tools you use.
The example you mentioned is actually doing two things: formatting the number by your specification and changing the data type to char. That means you cannot use it for further calculations.
Usually, with Oracle tools you would define the output formatting in the client tool (e.g. SQL*PLUS has the option to define formatting with the COLUMN setting).
The standard SAP HANA SQL clients like hdbsql or SAP HANA Studio take the formatting settings mainly from the OS environment and don't provide specific options to change the formatting.
For SAP HANA Studio I wrote a detailed explanation on what factors drive the formatting of dates and numbers, see here.