Q:

Parsing Street Address Oracle and remove unwanted texts

I'm newbie to Oracle and I have a requirement to parse US Street address from a string, I tried couple of options with regexp substr, but didn't give me desired result.
Here is few example from text string.
xyz 123 se stark st suit 401
xyx 123 39th ave ne 2nd flr n-3
xyz 213 nebraska courage way e-1  
And my output should be:
123 se stark st
123 39th ave ne
213 nebraska courage way  
My SQL so far :
SELECT UPPER (
          REGEXP_SUBSTR (REPLACE ('xyz 2551 38th ave ne tc 2', 'xyz '),
                         '((^[0-9]{1,9}+[ ]+[0-9]{1}[/]+[0-9]{1})|(^[0-9]+))'))
          AS houseno,  
       TRIM (
          UPPER (
             REGEXP_SUBSTR (
                REPLACE ('xyz 2551 38th ave ne tc 2', 'xyz '),
                '[^0-9]+((1( )st|1st)|(2( )nd|2nd)|(3( )rd|3rd)|([1234567890]+( )th|[1234567890]+th)) +(ave(nue)?|st(reet)?|boulevard|blvd)[[:print:]]*|P(.)O(.)( )+Box( )[0-9]+|[a-z][[:print:]]*',
                1,
                1,
                'i')))
          AS street_addrerss,    
       UPPER (REPLACE ('xyz 2551 38th ave ne tc 2', 'xyz ')) AS orginal_text
  FROM DUAL;

Thanks in advance!!

A:

Here is a somewhat general approach... it assumes the address begins at the first digit in the string (so the "useless" text before the address may never contain digits), and the address always ends with st, str, street, av, ave, avenue, way, rt, route, blvd, with or without a period after them. You will have to maintain this list, as there are many other possibilities.
It also assumes everything in the input string is in lower-case (if not, you can apply lower_case to the input string, and then initcase to the resulting address - I won't worry about that). If you DON'T want the terminal period (.), regardless of whether an address contains it or not, you can delete .? from the end of the search string.
with
     input_strings ( str ) as (
       select 'xyz 123 se stark st suit 401'     from dual union all
       select 'xyx 123 39th ave ne 2nd flr n-3'  from dual union all
       select 'xyz 213 nebraska courage way e-1' from dual union all
       select '2 new way blvd. apt 33'           from dual
     )
select regexp_substr(str, '\d.*(st|str|street|av|ave|avenue|way|rt|route|blvd).?') 
                                                                               as address
from   input_strings
;

ADDRESS
------------------------------
123 se stark st
123 39th ave
213 nebraska courage way
2 new way blvd.

