SQL ORACLE : Is it possible to convert NUMBER with CHAR (varchar2 datatype) to NUMBER datatype

Question

I have a big problem right now and I really need your help, because I can't find the right answer. I am currently writing a script that triggers a migration process from a table with raw data (data we received from an excel file) to a new normalized schema.

My problem is that there is a column PRICE (varchar2 datatype) with a bunch of traps. For example: 540S, 25oo , I200 , S000 .

And I need to convert it to the correct NUMBER(9,2) format so I can get: 5405, 2500, 1200, 5000 as NUMBER for the previous examples and INSERT INTO my_new_table.

Is there any way I can parse every CHAR of these strings that verify certain conditions? Or others better way?

Thank you :)!

question from:https://stackoverflow.com/questions/65872716/sql-oracle-is-it-possible-to-convert-number-with-char-varchar2-datatype-to-n

Answer 1

One of the wonderful things about Oracle that some other DBs lack, is the TRANSLATE function:

SELECT TRANSLATE(number, 'SsIilOoxyz', '5511100') FROM t

This will convert:

S, s to 5
I, i and l to 1
O, o to 0
Remove any x, y or z from the number

The second and third arguments to translate define what characters are to be mapped. If the first string is longer than the second then anything over the length of the second is deleted from the resulting string. Mapping is direct based on position:

'SsIilOoxyz', 
'5511100'

Look at the columns of the characters; the character above is mapped to the character below:

S->5, 
s->5, 
I->1, 
i->1, 
l->1, 
O->0, 
o->0,
x->removed, 
y->removed, 
z->removed`