SQL & PL/SQL

I want to extract zip from a address e.g. 'McLington, GA. 30226-5547, USA'.
I used this and it is working-

SELECT TRIM(TRANSLATE(REGEXP_REPLACE('McLington, GA. 30226, USA', '[[:alpha:]]',''),'.,',' '))
FROM DUAL;

Is there a simple (less expensive) way to do it?

Also it will not work if there is any other numeric character in this address portion.
OR worse is zip can be in mixed (numeric + non numeric) format. Any solution?

Address could be in any format like-
'McLington, GA. 30226-5547, USA'
'McLington, GA. 30226, USA'
'09880 Bupa, Italy'
'T5D-HJ3 MAINCHESTER, UK' -- This has zip in mixed (numeric + non numeric) format....another problem*****
'McLington-Poppsa, GA. 30226-5547, USA' **** '-' is creating problem.


Thanks.

--Rob

Edited by: roboracle on Sep 30, 2010 1:34 PM