Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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


TRIM(BOTH'0'FROMTO_CHAR(3,'FM000'))


3


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;
4 rows affected

SELECT *
FROM   table_name;


VALUE
TRIMMED_VALUE


0
null

3
3

30
3

100
1