3 Replies Latest reply: Jan 23, 2013 12:42 AM by michaelrozar17 RSS

    Question on regexp

    michaelrozar17
      Can somebody help me understand how the below regexp works..? I work in Oracle 10gR2
      select regexp_replace('121131','[^1]*','^') from dual
      Output: ^1^^1^1^^1^
        • 1. Re: Question on regexp
          Karthick_Arp
          michaelrozar17 wrote:
          Can somebody help me understand how the below regexp works..? I work in Oracle 10gR2
          select regexp_replace('121131','[^1]*','^') from dual
          Output: ^1^^1^1^^1^
          '[^1]*' Means 0 or more occurance of a character that is not 1

          So what your regular expression is doing is it is replace every character that is not 1 with ^. But the thing here is that you have used * which is 0 or more occurance. So every 0 occurance also get replaced with ^, and thats why you get ^ before every 1.

          Now try using + instead of * you will get this
          REGEXP_REPLACE('121131','[^1]+','^')
          ------------------------------------
          1^11^1 
          + means 1 or more occurance. so you can see that only the values that are not 1 are being replaced by ^
          • 2. Re: Question on regexp
            Manik
            In addition to Karthik's comments, if you want to play like inserting carat symbol in between the digits, you may use :
            SELECT REGEXP_REPLACE ('121131', '(.)', '\1^') FROM DUAL;
            output
            ------------------------

            1^2^1^1^3^1^

            Cheers,
            Manik.
            • 3. Re: Question on regexp
              michaelrozar17
              Oracle's regexp works quite different from Unix shell script's regexp, i was under the impression that both works in same way. However thanks for your time.
              Below is unix version:
              $ echo '121131' |sed 's/[^1]*/^/g'
              ^1^1^1^1^
              $