Oracle Transformation HELP!!

Hi,

How do I extract elements of a text field?
For example, In my customer table I have an attribute called CustAddress with example data: "17 Good Street, Manhattan, NY, 30004"

I want to extract those data into another table without losing any data to having attributes for each address detail, like, CustStreet, CustSuburb, CustState and CustZipCode.

How can I extract "17 Good Street" and put it into CustStreet attribute and so forth? Get what I mean?

Read this for instructions about substring and instring functions, then buid a view with the desired details.

Also how would you go about extracting an element of a text field and tranferring it into a number?
For example:

address="13 good st, NYC";

I want to extract the text "13" and changing its data type to a number, how can i do it?
Do i extract it by using substring function and put it into a temporary attribute then alter its data type then? or is there a more efficient way of doing this?

Thanks

You need to CAST it.