4 Replies Latest reply: Feb 12, 2013 4:24 PM by user11089573 RSS

    REGEXP_REPLACE question

    user11089573
      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
          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
            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
              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
                Thank you Frank!!