2

Possible Duplicate:
Add Currency Sign £, $ to certain fields ORACLE

I have a table of values that represent money, I want to prefix the SQL query output with a £ sign.

Is there anyway of doing this?

Community
  • 1
  • 1
Stacker-flow
  • 1,251
  • 3
  • 19
  • 39

2 Answers2

1

You can

SELECT to_char( column_name, '£999,999,999.99' ) 
  FROM table_name

assuming you want to hard-code the currency symbol and the formatting. If you want to use the session's NLS settings to determine the appropriate currency symbol and grouping symbol

SELECT to_char( column_name, 'L999G999G999D99' ) 
  FROM table_name

Breaking that down

  • L is the local currency symbol
  • G is the grouping symbol (a comma in the US)
  • D is the decimal separator (a period in the US)
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Could that also be used in conjunction with a AVG? This is my current query... SELECT car_type, AVG(caravan.rental_caravan) as Average FROM caravan_category JOIN caravan ON caravan.caravan_type_id = caravan_category.caravan_type_id GROUP BY car_type ORDER BY Average desc; – Stacker-flow Dec 10 '12 at 16:55
  • @user1887939 - Sure, you can nest the function calls (`to_char( avg( caravan.rental_caravan ), 'L999G999G999D99')`) – Justin Cave Dec 10 '12 at 17:15
  • Fantastic :D Great Help - VERY MUCH appreciated – Stacker-flow Dec 10 '12 at 17:21
0

Use something like: to_char(money, '£9999.00').

Details here.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • Duplicate of [Add Currency Sign £, $ to certain fields ORACLE](http://stackoverflow.com/questions/2717686/add-currency-sign-to-certain-fields-oracle) – Kermit Dec 10 '12 at 16:50