Skip to content
Advertisement

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.

returns GBP

returns 0.1

Concatenating the 2…

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

Advertisement

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:

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).

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement