REGEXP_LIKE ?? Should I use

Posted on 2011-10-17
Last Modified: 2012-05-12
I have been using the below code to do a key down filter using ajax but now I realize the regexp_like is not seeing the ( and ) as normal characters.
Should I be using this regexp_like here?
select group_name, group_id, rownum r from ( 
			 select group_name, group_id
			   from um_groups
			  where group_type_id = 2
			    and regexp_like( group_name,
				   nvl(l_search,group_name), 'i' )
			    and nvl(instr(':'||l_selected||':',
			    and status_id = nvl(l_status_filter,1)
			  order by 1)

Open in new window

Question by:bcarlis
    LVL 73

    Expert Comment

    if "l_search"  is a string like  this

    '(something)'     the ()  are regular expression delimiters to identify "something" as pattern.

    if you want to search for the parentheses characters themselves.  you must prefix them with "\"

    try modifying l_search like this...

    LVL 73

    Accepted Solution


    if the only thing you're trying to do is an existence check of some substring  "like" or "instr" might be better, even with an UPPER to handle your case insenstive searching

    and instr(upper(group_name), upper(nvl(l_search,group_name))) > 0

    regexp are fairly expensive compared to other string functions. Unless your string parsing is complex enough to warrant the cost of regular expressions, I'd avoid them.
    LVL 2

    Author Closing Comment

    Thank you!

    Featured Post

    Looking for New Ways to Advertise?

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

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    730 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

    15 Experts available now in Live!

    Get 1:1 Help Now