trim function not working on result of to_char

The query

SELECT TRIM(BOTH '0' FROM  '003') FROM DUAL;

gives the result 3 as expected but the query:

select TRIM(BOTH '0' from TO_CHAR(3,'000')) from dual;

gives 003

I need the result to be as the first query (‘3’) but I also need the ‘TO_CHAR’ (I’m using it to create a virtual column from a NUMBER column).

I can not figure why and how to fix it. Also, I can not use regex_replace instead.

Answer

You want to use the FM format model to not include the leading white space.

SELECT TRIM(BOTH '0' from TO_CHAR(3,'FM000'))
FROM   DUAL;

Which outputs:

| TRIM(BOTH'0'FROMTO_CHAR(3,'FM000')) |
| :---------------------------------- |
| 3                                   |

If you want a generated column then:

CREATE TABLE table_name (
  value         NUMBER,
  trimmed_value VARCHAR2(10)
                GENERATED ALWAYS AS (TRIM(BOTH '0' FROM TO_CHAR(value, 'FM000')))
);

INSERT INTO table_name ( value )
SELECT   0 FROM DUAL UNION ALL
SELECT   3 FROM DUAL UNION ALL
SELECT  30 FROM DUAL UNION ALL
SELECT 100 FROM DUAL;

Then:

SELECT *
FROM   table_name;

Outputs:

VALUE | TRIMMED_VALUE
----: | :------------
    0 | null         
    3 | 3            
   30 | 3            
  100 | 1            

db<>fiddle here