Adding value to start of string

Question

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?

The "+" is the problem. May be this comes from a java code, but in Oracle Pl/SQL the concatenation is done using ||. Also, hardcoding '£' is not a good practice - try to get it dynamically (from another table or somewhere else), as when you run this code for a client using a different currency, you will hardcode it again etc. — g00dy
– g00dy, Commented Oct 25, 2017 at 7:02

MT0 · Accepted Answer · 2017-10-24 21:18:50Z

2

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 |

answered Oct 24, 2017 at 21:18

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2017-10-24 19:16:11Z

0

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

answered Oct 24, 2017 at 19:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Adding value to start of string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related