This discussion is archived
8 Replies Latest reply: Mar 28, 2013 7:25 AM by Frank Kulash RSS

Regular Expressions

9876564 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    [: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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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