0

I have the following table:

Orders (o)

id   name            price       quantity
---------------------------------------------
1    Candy           0.69        5
2    Brownies        5.99        1

I'm currently doing this query:

SELECT o.id, CONCAT(o.price, ' * ', o.quantity) AS formula
FROM orders o

It works but I get the following result:

id    formula
---------------------------------------
1     .69 * 5
2     5.99 * 1

What changes to the query do I need in order to get 0.69 instead of .69?

dokgu
  • 4,957
  • 3
  • 39
  • 77

1 Answers1

2

You can use to_char() to convert a string to a number. A 0 includes leading zeros:

SELECT o.id,
       (TO_CHAR(o.price, '0.00') || ' * ' || o.quantity) AS formula
FROM orders o;

In Oracle, concat() only takes two arguments. || is the more traditional method of doing concatenation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Worked, thanks! What's the difference between using `'0.00'` instead of `'9999.99'` as SkyWalker mentioned? His solution didn't work for me but yours did. – dokgu Mar 09 '16 at 14:54
  • 1
    The difference in the format models is documented here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm – Michael Broughton Mar 09 '16 at 15:42
  • 1
    @PatrickGregorio . . . 9's in a numeric format just represent any digit, but no leading zeros. 0's are always there, even when the number is too short. – Gordon Linoff Mar 10 '16 at 00:55