What is the difference between LIKE clause of oracle and using  regexp?

Posted on 2014-08-14
Last Modified: 2014-08-20
What is the difference between LIKE clause of oracle and using  regexp?
Question by:Nusrat Nuriyev
    LVL 34

    Accepted Solution

    I can't give details on the complexities of how it will affect performance, but regular expressions (using REGEXP_LIKE) allow for much more complicated patterns than what LIKE does. Some details on what can be used in a REGEXP_LIKE pattern are here.

    For example, a regular expression can match a letter from a to m, followed by 3 or 4 numbers between 2 and 7:

    Open in new window

    This could not be done with a single use of LIKE.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Agree with the above post.  LIKE only uses wildcard operators, '%' and '_'.  Regular expressions are EXTREMLY powerful and can match just about anything you can dream up.

    Of course, that comes with a price.  Regular expression operations can be very expensive.

    LIKE can also use an index if there are no left sided wildcards.
    LVL 73

    Expert Comment

    as noted above, you can do more with regular expressions

    however, regexp operations are expensive (in terms of cpu and time) and can not, by default, be indexed.

    LIKE clauses on leading values can be indexed - for example   my_column like 'ABC%'  can use an index but
    my_column LIKE '%ABC' can not

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now