expression - How to extract specific values in a string in informatica

Question

I need to extract the (, ), - and numbers from 0 to 9 from the input field phone number.

For eg., for input data : (891)456-567A$rT67

Output should be (891)456-56767

question from:https://stackoverflow.com/questions/65878594/how-to-extract-specific-values-in-a-string-in-informatica

Answer 1

can be done in below 2 steps -

v_ph_no = REG_REPLACE( in_ph_no,'[^0-9]','')` -- variable port that will replace everything but 0 to 9.
out_ph_no = '(' || substr(v_ph_no,1,3)||')' || substr (v_ph_no,4,3)||'-' || substr (v_ph_no,6) -- o/p port that will create ph no in desired format.

Explanation - first remove all unwanted characters and then format it in desired phone no format.