deve_thomos
asked on
How to get this query ?
Hi Expert,
I am using oracle 11.1.2.0
below is my table
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
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.!@#$%^&*()_+=-{}][|\'';":/?.>,<');
Query:select * from SAMPLE_TEST
where id in (103664,
103665)
and regexp_instr(E_NAME
,nvl(:Name
,E_NAME)
,1
,1
,1
,'im') > 0;
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
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
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
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
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
ASKER
Thanks lot....
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.
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.
Based on your coworker's question here :
https://www.experts-exchange.com/questions/28511647/How-to-get-this-query.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
https://www.experts-exchange.com/questions/28511647/How-to-get-this-query.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
'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;