Zero lead digit for a number removed when concatenating in SQL

I have written a couple of PL SQL Functions to return a currency and amount from a compressed field. These work perfectly individually.

e.g.

Select currency(DATA)
from dual;

returns GBP

Select amount(DATA)
from dual;

returns 0.1

Concatenating the 2…

Select currency(DATA) || amount(DATA)
from dual; 

returns GBP.1

I want it to be GBP0.1, any idea what is going wrong?

The amount function returns a NUMBER the currency a VARCHAR2. It is Oracle SQL.

Thanks Chris

Answer

If you really want to pursue this the way you’re trying, you can format the number using TO_CHAR(your_number, '90.99').

Example:

SELECT 'GBP' || TO_CHAR(0.1, '90.99') FROM DUAL;

More formatting examples are detailed here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#BABFJEAA

Note that there is a way to extract the local currency, and that may be a better way to get GBP than whatever method you’re currently using (which you don’t provide in your sample).