I have my data in the given format
Column 1 Column 2
$10,500.00 Father (FA)
$165.00 Self (S)
$315.00 Self (S)
I need to convert column 1 to number and separate column 2 in 2 fields as shown below
Column 1 Column 2 Column 3
10500 Father FA
165 Self S
315 Self S
How can I do in Oracle
SELECT
REPLACE(REPLACE(SUBSTR(col1, 1, INSTR(co1, '.') - 1), '$', ''), ',', '') AS col1,
SUBSTR(col2, 1, INSTR(col2, ' ') - 1) AS col2,
SUBSTR(col2, INSTR(col2, '(') + 1, INSTR(col2, ')') - INSTR(col2, '(') - 1) AS col3
FROM yourTable
This answer assumes that both original columns contain text. If the first column is actually derived from a numeric column, then you should leverage Oracle's ability to format such data. But the second and third output columns could still use the same approach.
This could be a different approach, treating the first column as a number and using regular expressions to split the second column:
select to_number(Column1, 'L999G999G999D99', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = ''$'' '),
regexp_substr(Column2, '[^(]*'),
regexp_substr(Column2, '\((.*)\)', 1, 1, '', 1)
from test
