I have table , that has field named AMOUNT , amount takes number, table has 1.4m records, i need to upgrade them all . I would like the change NUMBER to varchar and make look like amount datas comma separated , eg: 76543-> 76,543. How can I able to do it?
Advertisement
Answer
i need to upgrade them all
Don’t; if you have a numeric value then store it as a NUMBER
.
I would like the change
NUMBER
tovarchar
and make look like amount datas comma separated , eg: 76543-> 76,543. How can I able to do it?
Just change how you are displaying the value rather than changing how you are storing the value.
If you have the table and data:
CREATE TABLE table_name ( amount NUMBER(12,0) ); INSERT INTO table_name ( amount ) VALUES ( 76543 );
If you want to do it in a SELECT
statement then use TO_CHAR
and include sufficient digits to format the largest number you can hold:
SELECT amount, TO_CHAR(amount, 'FM999G999G999G990') AS formatted_amount FROM table_name;
Outputs:
AMOUNT FORMATTED_AMOUNT 76543 76,543
If you want to do that in the table then add a virtual column:
ALTER TABLE table_name ADD formatted_amount VARCHAR2(16) GENERATED ALWAYS AS ( TO_CHAR(amount, 'FM999G999G999G990') );
Then, after adding the virtual column:
SELECT * FROM table_name;
Outputs:
AMOUNT FORMATTED_AMOUNT 76543 76,543
db<>fiddle here