How do I remove the $ sign and the comma from the annualsalary column like $50,000 to 50000?-CodePudding

I have a column named annualsalary with varchar2 data type. In this column, I have values like $25,000, $67,000, etc. I want to get rid of both $ sign and the comma. I am able to do only one replace function and get rid of the dollarsign. But how do I get rid of the comma in the same query?

I tried doing it with the following query but I'm unable to get rid of comma.

SELECT REPLACE(ANNUALINCOME,'$','') AS column_variable FROM table_name;

It got rid of the $ sign and showed me the output as 50,000. But now I want to remove comma as well to make it 50000.

CodePudding user response：

Nest replaces

SELECT REPLACE(REPLACE(ANNUALINCOME,'$',''), ',', '') AS column_variable 
FROM table_name;

CodePudding user response：

You can use TO_NUMBER:

SELECT TO_NUMBER(value, '$999G999G999D99') AS amount
FROM   table_name;

Note: this has advantages over REPLACE in that it only requires a single function call and, more importantly, it converts the value from a string to a number so that if you sort the data then it will be sorted numerically and not alpha-numerically.

Note 2: You should store ANNUALINCOME as a NUMBER data type and not as a formatted string and can apply the appropriate formatting using TO_CHAR when you want to display the value rather than doing it reversed.

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '$25,000' FROM DUAL UNION ALL
SELECT '$67,000' FROM DUAL

Outputs:

AMOUNT

25000

67000

AMOUNT
25000
67000

db<>fiddle here