4 Replies Latest reply: Apr 3, 2013 6:56 PM by 996454 RSS

    Using the REGEXP_REPLACE function

    996454
      I'm trying to get an understanding of the REGEXP_REPLACE function by going over this question and answer:

      Question: Some employees have entered the product_code incorrectly. It should appear as a series of characters followed by a slash followed by another series of characters.
      Instead of putting the single slash / that separates the two groups of characters, some employeess entered spaces, hyphens or both.

      Format all product codes by removing all the extra spaces and hyphens and replacing them with a single /

      Answer: REGEXP_REPLACE (product_code, '(( ) {1, } | (-)) {1, } ', '/')

      I am confused about the placement of brackets in the function as well as what the single pipe does?

      I am guessing it is the same as an OR operator but I'm not sure.

      I have read the oracle documentation on REGEXP_REPLACE but I'm still unsure.
        • 1. Re: Using the REGEXP_REPLACE function
          Frank Kulash
          Hi,
          993451 wrote:
          I'm trying to get an understanding of the REGEXP_REPLACE function by going over this question and answer:

          Question: Some employees have entered the product_code incorrectly. It should appear as a series of characters followed by a slash followed by another series of characters.
          Instead of putting the single slash / that separates the two groups of characters, some employeess entered spaces, hyphens or both.

          Format all product codes by removing all the extra spaces and hyphens and replacing them with a single /

          Answer: REGEXP_REPLACE (product_code, '(( ) {1, } | (-)) {1, } ', '/')
          Are you sure that's the answer given? It doesn't work when I try it.
          It looks like there are extra spaces, and I suspect some of the round parentheses should really be square brackets, or are not really needed.

          As I understand the problem, this is one solution:
          REGEXP_REPLACE ( product_code
                      , '[ -]+'
                      , '/'
                      ) 
          The square brackets enclose a set of characters. In this case, the set contains 2 members: space and hyphen. The + sign means that we are looking for 1 or more characters that are in the set.
          I am confused about the placement of brackets in the function as well as what the single pipe does?

          I am guessing it is the same as an OR operator but I'm not sure.
          Yes, the vertical pipe means "OR".
          I have read the oracle documentation on REGEXP_REPLACE but I'm still unsure.
          Yes, the Oracle documentation is designed as a reference, not a tutorial, and the parts about regular expressions are not the best the Oracle has published.
          Try these sites:
          http://www.sqlsnippets.com/en/topic-10759.html
          http://www.dba-oracle.com/t_regular_expressions.htm
          Introduction to regular expressions ... last part.
          • 2. Re: Using the REGEXP_REPLACE function
            mojave
            That's a Transcender 1Z0-047 question. As Frank found, don't count on their answers to work, the most fun you can have with Transcender is creating test data and playing around with their answers, coming up with your own approach. This is a good one to learn about dividing something into sub-expressions and replacing one of the subexpressions.
            • 3. Re: Using the REGEXP_REPLACE function
              996454
              Excellent! Cheers for the resources.
              create table productcode (
              productcode varchar2(16) not null
              );
              /
              
              insert into productcode values ('STAT EDS');
              insert into productcode values ('STAT-EDS');
              insert into productcode values ('STAT-EDS');
              insert into productcode values ('STAT/EDS');
              insert into productcode values ('STAT  EDS');
              
              ----
              select * from student_exams;
              
              
              
              select REGEXP_REPLACE ( productcode, '[ -]+', '/') as newcode
              from productcode;
              Works good.
              • 4. Re: Using the REGEXP_REPLACE function
                996454
                Yip you're right. I thought they would have checked over the answers.