Function format number and how to use it in sql statement

Question

For oracle,

If I have a table A with data in number (such as price), how to write a function to format a number(10,2) to 9,9999.99 (<< obvious format).

And then how to use this function in SQL statement for displaying the data in table A.

Thank you.

----- UPDATE ----- From kindly suggestions below, yes, I should not treat numbers in this way. But I highly want to accomplish the task above

Here I come with the function

    CREATE OR REPLACE FUNCTION Fmt_num(N1 in NUMBER)
    RETURN CHAR
    IS
    BEGIN
    RETURN TO_CHAR(N1,'FM9,9999.99');
    END;
    /

And I can use this with the SQL statement as follow

    SELECT Fmt_num(price) from A;

It works ! However, Can anyone fixes the function above to let it works with "a number (10,2)"? Just this condition only. Thank you for your anticipate in this noob question. :)

Possible duplicate

Prahalad Gaggar
– Prahalad Gaggar

2013-05-21 08:05:57 +00:00
Commented May 21, 2013 at 8:05 — Prahalad Gaggar
– Prahalad Gaggar, Commented May 21, 2013 at 8:05

Ronnis · Accepted Answer · 2013-05-21 08:07:32Z

1

Look at TO_CHAR function.

select to_char(price, 'FM9,999,999.99')
  from A;

answered May 21, 2013 at 8:07

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niick Mosqow Over a year ago

Thank you for the example. Btw, this is a query. Is there possible to create a function first and then pull that function into this query?

Ronnis Over a year ago

You can do it, but I would not recommend it. Encode the formatting in your end-user application instead.

Niick Mosqow Over a year ago

Thank you for suggestion. If you don't mind can you show me the example (for my obvious task), would be a good knowledge for me. :) Thank you

Collectives™ on Stack Overflow

Function format number and how to use it in sql statement

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related