String Conversions Examples

--1. Convert the number 56346.213 to the string '$56,346.21' (only 2 decimal places).
--
--2. Add the string 'The figure is ' to the string '$56,346.21'.
--
--3. Convert the string '$56,346.21' to the number 56346.21.

–1. Convert the number 56346.213 to the string ‘$56,346.21’ (only 2 decimal places)

–1
–fm infront of the format can also be used to remove extra spaces.. such as “‘fmL99G999D99′”
SELECT TO_CHAR(56346.213, ‘L99G999D99’
, ‘NLS_NUMERIC_CHARACTERS = ”.,” ‘) AS NEW_NUMBER
FROM DUAL;

–2 Add the string ‘The figure is ‘ to the string ‘$56,346.21’. I solved this by adding a LTRIM
SELECT ‘The figure is ‘ || LTRIM(TO_CHAR(56346.213, ‘L99G999D99’
, ‘NLS_NUMERIC_CHARACTERS = ”.,” ‘) ) AS NEW_NUMBER
FROM DUAL;

–3. Convert the string ‘$56,346.21’ to the number 56346.21.
SELECT LTRIM(TO_NUMBER(‘$56,346.21’, ‘L99999D99’), ‘ ‘) AS NEW_NUMBER
FROM DUAL;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top