Get only number from a string after a particular letters or symbol

[MK9885]

Hi,

I have a string coming in from DB as an example

John Doe address is 1234 Street ABC City NY 98756 & 45612

Here I'd just like to get numbers after NY, but not the symbol '&' just numerics

Would like to create 2 fields out of above string

1st field should have 98756

2nd field should have 45612

Tried len & keepchar, doesn't work or maybe I'm missing something.

Any help is appreciated @sunny_talwar  or anyone else..

[MK9885]

Thank you but if you see Zip1 & Zip2 fields are both same.
In your image, I'd need 71275 as Zip1 & 75635 as Zip2.
Also Zip1 & Zip2 should only be these numbers. Not contain any alphabets.

Edit:

replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ')

,-1))),' ',2)as Zip2

I replaced 1 with 2 with zip2 it worked but some of it still has values such as CPT, ) etc

[MK9885]

Is there a way to keep "TC" after a number?
some of the numbers have TC, like 74183-TC
so Zip1 gives me just 74183 but I'd need 74183-TC