Search code examples
sqloracle-databaseregexp-replace

regexp_replace string

I'm using regexp_replace to standardize mailing addresses and I've encountered a situation I'm having trouble with.

Consider the following two addresses and what their result should be:

  • 115 1/2 East 6th St -> 115 1/2 E 6th St
  • 818 East St -> 818 East St

In the second address, "East" is the actual name of the street, not a directional indicator.

For my query, I've attempted 

SELECT
     regexp_replace(address, 'East[^ St]', 'E ')

but this fails to convert the first address to it's proper format.

How can I write my regexp_replace such that the word East is converted to an 'E' in the first address, but leaves the word intact in the second address?

Solution

  • Your current pattern matches the literal text East followed by any single character that isn't space, S, or t. I'm assuming you probably meant to use a negative lookahead to make sure that "East" doesn't come before " St", but sadly Oracle doesn't support negative lookaheads. Instead, you'll need to make the REGEXP_REPLACE conditional:

    CASE
    WHEN address LIKE '%East%' AND address NOT LIKE '%East St%'
        THEN REGEXP_REPLACE(address, your_pattern, your_replacement)
    ELSE address
END