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