This discussion is archived
4 Replies Latest reply: Feb 12, 2013 2:24 PM by user11089573 RSS

REGEXP_REPLACE question

user11089573 Newbie
Currently Being Moderated
Hi Everyone,

I have a unique scenario in where I need to use the regexp_replace function to accomodate what the client is looking for. Here is the scenario, they have field for an address that has ''1000 Jefferson's Hou'snty Parkway" and they want the 's' in Jefferson's to stay lower case but the 's' in Cou'snty should be upper case. So the regexp_replace function needs to look at the string '1000 Jefferson's Hou'snty Parkway' and understand that if the 's is at the very end then it needs to be lower case but the other one needs to be 'S.

Here is what I have so far but it only takes care of the Jefferson's scenario but not the Hou'snty word. Can you help me in writing the correct expression for this scenario?

select regexp_replace(ADDRESS_1, '''S$','''s')
from MY_TABLE

Thanks for your help.
  • 1. Re: REGEXP_REPLACE question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    SELECT     REGEXP_REPLACE ( address_1
                     , '''s(\S)'
                     , '''S\1'
                     )          AS new_adddress_1
    FROM    my_table
    ;
    '\S' means some character other than a space or tab, so the expression above does not match when the apostrophe-s is followed by a space or tab (or end-of-string).

    Edited by: Frank Kulash on Feb 12, 2013 4:40 PM
  • 2. Re: REGEXP_REPLACE question
    user11089573 Newbie
    Currently Being Moderated
    Thank you Frank. I just tested it out and it is working! But I'd like to fully understand the expression you wrote.

    What is this 's(\S)' and '''S\1' doing?

    Thanks
  • 3. Re: REGEXP_REPLACE question
    Frank Kulash Guru
    Currently Being Moderated
    user11089573 wrote:
    Thank you Frank. I just tested it out and it is working! But I'd like to fully understand the expression you wrote.

    What is this 's(\S)' and '''S\1' doing?
    In the 3nd argument, '\1' is a Backreference . It means the part of the expression in the 2nd argument inside the 1st left '(' and its matching right ')'. In this case, that means the non-spacing character (space, tab, newline) that matched \S

    It might be easier to understand with some in-line comments:
    SELECT     REGEXP_REPLACE ( address_1
                     , '''s'   || -- apostrophe and lower-case s
                           '(\S)'         -- \1, which is any non whitespace character
                     , '''S'      || -- apostrophe and capital-S
                       '\1'         -- whatever \1 was
                     )          AS new_adddress_1
  • 4. Re: REGEXP_REPLACE question
    user11089573 Newbie
    Currently Being Moderated
    Thank you Frank!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points