REGEXP_LIKE vs LIKE [message #382328] |
Wed, 21 January 2009 23:41  |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Hi All,
I am using REGEXP_LIKE instead of Like as my comparasion data is saved in one table.
And i am passing values in stored procedure and check if it matches pattern in the table.
For eg.
Table can have data
abc
*abc*
abc*
*abc
*a*b*c*
In Stored procedure i will be passing values 'xaybzc' and i will check if this pattern is present in table.
But problem is i am not getting desirable result.
If i would be using LIKE my syntax will be
1. abc : 'xaybzc'like 'abc'
2.%abc% : 'xaybzc'like '%abc%'
3.abc% : 'xaybzc'like 'abc%'
4.%abc : 'xaybzc'like '%abc'
5.%a%b%c% : 'xaybzc'like '%a%b%c%'
To Convert same logic in REGEXP_LIKE i am using
1.^abc$ : regexp_like('xaybzc',^abc$)
2.abc : regexp_like('xaybzc',abc)
3.abc* : regexp_like('xaybzc',^abc*)
4.*abc : regexp_like('xaybzc',*abc$)
5.*a*b*c : regexp_like('xaybzc',*a*b*c*)
Steps 3 to 5 are not working properly. Could any one give me correct way to compare this kind of pattern.
Regards
Manish
-
Attachment: Text3.txt
(Size: 0.97KB, Downloaded 766 times)
|
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382340 is a reply to message #382330] |
Thu, 22 January 2009 00:13   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
--Creating Table
create table test
(
compare varchar2(10)
);
--Inserting Values
insert all
into test values ('abc')
into test values ('*abc*')
into test values ('abc*')
into test values ('*abc')
into test values ('*a*b*c*')
select * from dual;
--Create Function to match pattern
CREATE OR REPLACE FUNCTION TRANSFORM_TO_REGEXP_LIKE(P_ACTPARAM VARCHAR2)
RETURN VARCHAR2
IS
V_MODPARAM VARCHAR2(50);
BEGIN
V_MODPARAM:=P_ACTPARAM;
IF SUBSTR(V_MODPARAM,1,1) NOT IN ('*','?') THEN
V_MODPARAM:='^'||P_ACTPARAM;
ELSIF SUBSTR(V_MODPARAM,1,1) IN ('*') THEN
V_MODPARAM:=substr(V_MODPARAM,2);
END IF;
IF SUBSTR(V_MODPARAM,-1,1) NOT IN ('*','?') THEN
V_MODPARAM:=V_MODPARAM||'$';
ELSIF SUBSTR(V_MODPARAM,-1,1) IN ('*') THEN
V_MODPARAM:=substr(V_MODPARAM,1,length(V_MODPARAM)-1);
END IF;
V_MODPARAM:=REPLACE(V_MODPARAM,'?','.');
RETURN V_MODPARAM;
END;
/
--This Works
SELECT * FROM test WHERE regexp_like('abc',TRANSFORM_TO_REGEXP_LIKE(compare)) and compare='abc'
--This Works
SELECT * FROM test WHERE regexp_like('abc',TRANSFORM_TO_REGEXP_LIKE(compare)) and compare='*abc*'
--This Works
SELECT * FROM test WHERE REGEXP_LIKE('abcsdf',TRANSFORM_TO_REGEXP_LIKE(compare)) and compare='abc*'
--This Works
SELECT * FROM test WHERE REGEXP_LIKE('sdfabc',TRANSFORM_TO_REGEXP_LIKE(compare)) and compare='*abc'
--This Doesn't Work
SELECT TRANSFORM_TO_REGEXP_LIKE(compare) FROM test WHERE REGEXP_LIKE('sdfabc',TRANSFORM_TO_REGEXP_LIKE(compare)) and compare='*a*b*c*'
Regards
Manish
|
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382353 is a reply to message #382328] |
Thu, 22 January 2009 00:38   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Hi,
How to use REGEXP_LIKE for the below query
select * from dual where 'abcdef' like 'a%f';
I have tried this
select * from dual where regexp_like('abcdef','^a*f$');
But it is not working.
Regards
Manish H
|
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382362 is a reply to message #382360] |
Thu, 22 January 2009 00:49   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Hi,
How to use REGEXP_LIKE for the below query
select * from dual where 'abcdef' like 'a%f';
I have tried this
select * from dual where regexp_like('abcdef','^a*f$');
But it is not working.
Regards
Manish H
|
|
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382368 is a reply to message #382364] |
Thu, 22 January 2009 00:58   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
I want to find values starting with letter a and ending with letter f and having any number of characters in between them.
if i use like it will be as below and it gives output
SQL> select * from dual where 'abcdef' like 'a%f';
D
-
X
I want to know how to convert above query by using REGEXP_LIKE.
SQL> select * from dual where regexp_like('abcdef','^a*b$');
no rows selected
I hope this time i am clear.
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382371 is a reply to message #382328] |
Thu, 22 January 2009 01:05   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
I got the answer.
Thanks for your support.
SQL> select * from dual where regexp_like('abcdef','^a.*f$')
2 /
D
-
X
SQL> select * from dual where regexp_like('xabcdef','^a.*f$')
2 /
no rows selected
SQL> select * from dual where regexp_like('abcde','^a.*f$')
2 /
no rows selected
SQL> select * from dual where regexp_like('abcdefe','^a.*f$')
2 /
no rows selected
|
|
|
|
Re: REGEXP_LIKE vs LIKE [message #382414 is a reply to message #382376] |
Thu, 22 January 2009 03:47   |
manish_getbusy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
I was stucked on this issue for pretty long time. So i couldn't wait for your response. So i posted the same in other sites too and i get the answer so just wanted to share the answer.
I am sorry if you feel bad.
|
|
|
|