regex parsing address to not include apt or suite at end

Question

I have numerous addresses that I need to match against each other (mother/children) some addresses have apt, suite etc etc at the end of the line-Been trying to find ways to get entire line w/o that last part-

with addy as (select '22 W JAMESTOWN ST APT 22' as addy from dual union 
              select '22 W JAMESTOWN ST 22' as addy from dual)
select addy.*,regexp_substr(addy,'(\d*)(\D*)(\s)',1,1,'i') as no_ from addy;

Final result should be: 22 W JAMESTOWN ST This is in oracle- The look ahead function does not seem to work- '/.+?(?=APT)/' has no effect

The first line works, the 2nd does not. Any input appreciated- TIA Lawrence

score 0 · Accepted Answer · answered Oct 01 '20 at 18:01

0

Instead of REGEX_SUBSTR, you can try REGEX_REPLACE to remove the extra information from the end of the address.

Query

WITH
    addy
    AS
        (SELECT '22 W JAMESTOWN ST APT 22' AS addy FROM DUAL
         UNION
         SELECT '22 W JAMESTOWN ST 22' AS addy FROM DUAL)
SELECT addy.*, REGEXP_REPLACE (addy, '\s?(APT)?\s?\d+$') AS no_
  FROM addy;

Result

                       ADDY                  NO_
___________________________ ____________________
22 W JAMESTOWN ST 22        22 W JAMESTOWN ST
22 W JAMESTOWN ST APT 22    22 W JAMESTOWN ST

answered Oct 01 '20 at 18:01

EJ Egyed

5,791
1
8
23

Thanks. That works. Quick question-how do I make that case insensitive? – Lawrence Block Oct 01 '20 at 18:06
Just wrap `UPPER` around `addy` in the `REGEXP_REPLACE` like this: `REGEXP_REPLACE (upper(addy), '\s?(APT)?\s?\d+$')` – EJ Egyed Oct 01 '20 at 18:08
For the sake of completeness REGEXP_REPLACE has a case insensitive option, but it requires putting in all the arguments: `REGEXP_REPLACE (addy, '\s?(APT)?\s?\d+$', NULL, 1, 1, 'i')` – Gary_W Oct 06 '20 at 14:15

regex parsing address to not include apt or suite at end

1 Answers1

Query

Result