Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE vs LIKE (Oracle 10g)
REGEXP_LIKE vs LIKE [message #382328] Wed, 21 January 2009 23:41 Go to next message
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 191 times)
Re: REGEXP_LIKE vs LIKE [message #382330 is a reply to message #382328] Wed, 21 January 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: REGEXP_LIKE vs LIKE [message #382340 is a reply to message #382330] Thu, 22 January 2009 00:13 Go to previous messageGo to next message
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 #382346 is a reply to message #382328] Thu, 22 January 2009 00:19 Go to previous messageGo to next message
manish_getbusy
Messages: 8
Registered: January 2009
Junior Member
Want to add one more thing.
In my first mail i have mentioned step 3 to 5 are not working.
Actually Step 5 alone is not working. Rest all are working fine.

Re: REGEXP_LIKE vs LIKE [message #382353 is a reply to message #382328] Thu, 22 January 2009 00:38 Go to previous messageGo to next message
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 #382360 is a reply to message #382353] Thu, 22 January 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you tried and why "it does not work".


Regards
Michel
Re: REGEXP_LIKE vs LIKE [message #382362 is a reply to message #382360] Thu, 22 January 2009 00:49 Go to previous messageGo to next message
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 #382364 is a reply to message #382362] Thu, 22 January 2009 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat your previous post? Do you want me to repeat mine?

"a*" means "a" 0 or more times.

Regards
Michel

[Updated on: Thu, 22 January 2009 01:17]

Report message to a moderator

Re: REGEXP_LIKE vs LIKE [message #382365 is a reply to message #382328] Thu, 22 January 2009 00:53 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>But it is not working.
My motorscooter is not working.

You need to tell me how to make it work!
Re: REGEXP_LIKE vs LIKE [message #382368 is a reply to message #382364] Thu, 22 January 2009 00:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #382376 is a reply to message #382371] Thu, 22 January 2009 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

If you don't want to help us help you, you will have no more help.

Regards
Michel
Re: REGEXP_LIKE vs LIKE [message #382414 is a reply to message #382376] Thu, 22 January 2009 03:47 Go to previous messageGo to next message
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.
Re: REGEXP_LIKE vs LIKE [message #382415 is a reply to message #382414] Thu, 22 January 2009 03:52 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i get the answer so just wanted to share the answer.

And this is appreciated.

Regards
Michel
Previous Topic: passing string value into a cusrsor
Next Topic: Sunday in Month
Goto Forum:
  


Current Time: Fri Dec 09 11:47:50 CST 2016

Total time taken to generate the page: 0.12371 seconds