8 Replies Latest reply: Mar 28, 2013 9:25 AM by Frank Kulash RSS

    Regular Expressions

    9876564
      Dear All,

      Could some please explain me why the out put of the below expression is 1

       select REGEXP_INSTR('A','[[:alpha:]]') from dual 
      I am having difficulties in understanding the the use of Regular expression as we have other methods to achive the same result , when we should we opt for the regular Expressions?

      Thanks
        • 1. Re: Regular Expressions
          S10390
          [:alpha:]     is for Alphabetic characters. REGEXP_INSTR will give you the position of the Alphabet with the pattern ([:alpha:]) specified.

          Similar post...

          How to use REGEXP_INSTR

          Check this too
          http://psoug.org/reference/regexp.html
          • 2. Re: Regular Expressions
            jeneesh
            9999999 wrote:
            Dear All,

            Could some please explain me why the out put of the below expression is 1

             select REGEXP_INSTR('A','[[:alpha:]]') from dual 
            It is searching for an alphabet. Your string contains an alphabet at position 1. So is the result..

            >
            I am having difficulties in understanding the the use of Regular expression as we have other methods to achive the same result , when we should we opt for the regular Expressions?
            In my opinion, you should use REGEXP functions, only if it is inevitable.. REGEXP functions are more CPU consuming compared to other string functions..

            Thumb rule can be: if you can achieve the same result by other string functions (SUBSTR, INSTR...) use those functions.. When the requirement is very complex, use REGEXP..

            Read this : {message:id=9360010}
            • 3. Re: Regular Expressions
              Frank Kulash
              Hi,
              9999999 wrote:
              Dear All,

              Could some please explain me why the out put of the below expression is 1

               select REGEXP_INSTR('A','[[:alpha:]]') from dual 
              REGEXP_INSTR (str, pattern) returns an integer, the position where the pattern (the 2nd argument) is found in str (the 1st argument).
              [[:alpha:]] is the set of all alphbetic characters.
              'A' is an alphabetic character, so the position of the first alphabetic character in the string 'A' is 1.

              If you don't understand why that returns 1, what do you think it should return? What is there in the documentation that makes you think that?
              I am having difficulties in understanding the the use of Regular expression as we have other methods to achive the same result , when we should we opt for the regular Expressions?
              When there are othe methods to achieve the same results, and they do not involve a lot more coding than regular expressions, then it is usually faster not to use regular expressions; the other method will probably be more efficient. Of course, sometimes convenience is more important than performance.

              Sometimes the other method involves 2 or more times as much coding as the regular expression. There's a good chance that the regular expression will be more efficient in that case. For example; to replace all sub-strings of 2 or more consecutive spaces with a single space, you can use
              REGEXP_REPLACE (str, ' +', ' ')
              You can also use plain old REPLACE, but it takes 3 nested REPLACE calls, and it's very confusing.
              • 4. Re: Regular Expressions
                Rahul_India
                REGEXP_REPLACE (str, ' +', ' ')
                Hi frank
                select
                REGEXP_REPLACE ('str+','+','@')
                 from dual
                is giving me weird o/p
                @s@t@r@+@
                shouldn't i be getting str@
                • 5. Re: Regular Expressions
                  Manik
                  Rahul India wrote:
                  REGEXP_REPLACE (str, ' +', ' ')
                  Hi frank
                  select
                  REGEXP_REPLACE ('str+','+','@')
                  from dual
                  is giving me weird o/p
                  @s@t@r@+@
                  shouldn't i be getting str@
                  escape it
                  select
                  REGEXP_REPLACE ('str+','\+','@')
                   from dual
                  Output:
                  -----------------

                  str@

                  Cheers,
                  Manik.
                  • 6. Re: Regular Expressions
                    jeneesh
                    The below will replace multiple occurrences of space by a single space..
                    REGEXP_REPLACE (str, ' +', ' ')
                    Your query is doing something meaning less - It will probably replace nothing with @, so it will add @ everywhere..
                    REGEXP_REPLACE ('str+','+','@')
                    If you want to replace "+" with "@", you can use
                    REGEXP_REPLACE ('str+','\+','@')
                    This will escape the special meaning of +

                    the same thing can be done with REPLACE
                    • 7. Re: Regular Expressions
                      sybrand_b
                      The + sign is a special character. If you want to replace the + sign by @, you need to escape it by using \, so '\+'.

                      --------------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: Regular Expressions
                        Frank Kulash
                        Hi,
                        Rahul India wrote:
                        REGEXP_REPLACE (str, ' +', ' ')
                        Hi frank
                        select
                        REGEXP_REPLACE ('str+','+','@')
                        from dual
                        is giving me weird o/p
                        @s@t@r@+@
                        shouldn't i be getting str@
                        No. Others have already explained why.

                        This is a great example of when NOT to use regualr expressions. It looks like you want
                        REPLACE ('str+', '+', '@')
                        which is slightly easier to code than the equivalent regular expression, and more efficient.