Solved

How to get this query ?

Posted on 2014-09-04
8
266 Views
Last Modified: 2014-09-05
Hi  Expert,
I am using oracle 11.1.2.0

below is my table

create table sample_test(e_id number(10), e_name varchar2(4000));

insert into SAMPLE_TEST (E_ID,E_NAME) values (103664,'XYZ., Ltd.');
insert into sample_test (e_id,e_NAME) values (103665,'XYZ., Ltd.!@#$%^&*()_+=-{}][|\'';":/?.>,<');

Open in new window

Query:
select * from  SAMPLE_TEST
where id in (103664,
 103665)
and regexp_instr(E_NAME
                    ,nvl(:Name
                        ,E_NAME)
                    ,1
                    ,1
                    ,1
                    ,'im') > 0; 

Open in new window



when i am running  above query(i am passing null value in parameter) , i am getting below error

ORA-12726: unmatched bracket in regular expression
12726. 00000 -  "unmatched bracket in regular expression"
*CAUSE:    the Regular Expression Did Not Have Balanced Brackets.
*Action:   Ensure the brackets are correctly balanced.

Regards
Thomos
0
Comment
Question by:deve_thomos
  • 6
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40303768
Using E_NAME as the pattern to regexp_instr it has to be a valid pattern.

This row has brackets in the pattern:
insert into sample_test (e_id,e_NAME) values (103665,'XYZ., Ltd.!@#$%^&*()_+=-{}][|\'';":/?.>,<');

You need to escape them:
insert into sample_test (e_id,e_NAME) values (103665,'XYZ., Ltd.!@#$%^&*()_+=-{}\]\[|\'';":/?.>,<');

that said:
What you posted really doesn't make sense.

What are you trying to do?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40303773
change this

'XYZ., Ltd.!@#$%^&*()_+=-{}][|\'';":/?.>,<'

to this

'XYZ., Ltd.!@#$%^&*()_+=-{}]\[|\'';":/?.>,<'


the problem is the [ which starts a list expression, if you don't intend to create one then you'll need to escape it with the slash


or,  more likely, since your NVL looks like you are trying to do a wild card self-match then try this instead and then you won't have to modify your data


SELECT *
  FROM sample_test
 WHERE e_id IN (103664, 103665)
   AND REGEXP_INSTR(
           e_name,
           NVL( :name, '.'),
           1,
           1,
           1,
           'im'
       ) > 0;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40303792
another option, check for null explicitly outside of the regexp function


SELECT *
  FROM sample_test
 WHERE e_id IN (103664, 103665)
   AND ( :name IS NULL
     OR REGEXP_INSTR(
            e_name,
            :name,
            1,
            1,
            1,
            'im'
        ) > 0)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40303799
And one more suggestion,  rather  than using regexp_instr, you might want to try regexp_like for a little simpler syntax


SELECT *
  FROM sample_test
 WHERE e_id IN (103664, 103665) AND ( :name IS NULL OR REGEXP_LIKE(e_name, :name, 'im'))


Note - both this suggestion and the previous one with the external NULL check should be more efficient but have the side effect that they will match NULL e_names as well which the "." version I suggested first will not.

Assuming your e_names are not null, then they are all functionally equivalent
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40303839
one more thing that occured to me

searching mid-string is not an efficient operation because it's not something you can build a normal index for.

If your data is big, you might want to look into using a text index and changing the query to use CONTAINS.

CONTAINS isn't the same thing as a regular expression parser but it could give better performance for basic substring searches
0
 

Author Closing Comment

by:deve_thomos
ID: 40304060
Thanks lot....
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40304071
are you sure you want to try to modify your data?

adding the slash in this one example fixes that one row.

if you have other characters, for example the "." following "Ltd."   that period is not actually a period for the purposes of your matching.  It's really a wild card

 Is that really what you intended?  If not, then you probably don't want to go with a row-at-a-time data fix strategy.  Instead, try fixing the query with one of the options suggested.

or, maybe I'm misunderstanding what you were really looking for because the accepted answer does not seem correct for a real-world solution.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40306520
Based on your coworker's question here :

http://www.experts-exchange.com/Q_28511647.html

It looks like you two are pursuing CONTAINS rather than corrupting your data as a solution.

 That's probably a good idea but it would indicate this question was closed improperly
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Array of Key/Value Pairs as input to Oracle function 10 53
System.InvalidCastException: Specified cast is not valid 10 67
history tablespace temp usage 2 30
Oracle SQL 6 47
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

914 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

18 Experts available now in Live!

Get 1:1 Help Now