CODEWITHSUNDEEP
sqloracle-databaseoracle11g
James Pavett
James Pavett

Reputation: 43

Adding value to start of string

I am working with price lists, and am trying to add a £ to the start of my values. This is being required from an Oracle 11g database.

to_char('£' + (case when web_cost.cost is null then 0 else 
round(web_cost.cost,2) end)) as unitcost

web_cost.cost should produce values in the following format; 11.50, or 0 or null values possibly.

The above query is giving me invalid number error, I have even tried to_char on every section of this code, and have still had no luck.

This is the only section of my query that currently does not work, any ideas?

Upvotes: 0

Views: 76

Answers (2)

MT0
MT0

Reputation: 167867

Use TO_CHAR with the format model L to show the currency. If your default currency is not £ then you can supply the 3rd argument to set the NLS_CURRENCY:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE web_cost ( cost ) AS
SELECT 1234.567   FROM DUAL UNION ALL
SELECT 98765      FROM DUAL UNION ALL
SELECT 34567.8901 FROM DUAL UNION ALL
SELECT NULL       FROM DUAL;

Query 1:

SELECT TO_CHAR(
         COALESCE( cost, 0 ),
         'FML999990D00',
         'NLS_CURRENCY=£'
       ) AS unitcost
FROM   web_cost

Results:

|  UNITCOST |
|-----------|
|  £1234.57 |
| £98765.00 |
| £34567.89 |
|     £0.00 |

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You should use concat || for join the string 

select '£' || to_char(case when web_cost.cost is null then 0 else 
      round(web_cost.cost,2) end)) as unitcost

Upvotes: 0

Related Questions