Home » SQL & PL/SQL » SQL & PL/SQL » Use of REGEXP_LIKE (10g)
Use of REGEXP_LIKE [message #338529] Tue, 05 August 2008 05:20 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

I need to know which would be feasible in 10g.


select  distinct(CPR_ID)  from CPR_TBL where REGEXP_LIKE(CPR_ID,'[J|M]*.zip$')


SELECT Distinct (CP_ID) from CPR_TBL WHERE ( CPR_ID like 'J%.zip' or  CPR_ID like 'M%.zip')


Please kindly let me know if anything found wrong.


Regards,
Oli

Re: Use of REGEXP_LIKE [message #338540 is a reply to message #338529] Tue, 05 August 2008 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are not equivalent.
SQL> with CPR_TBL as (select 'AJ1.zip' CPR_ID from dual)
  2  select  distinct(CPR_ID)  from CPR_TBL where REGEXP_LIKE(CPR_ID,'[J|M]*.zip$')
  3  /
CPR_ID
-------
AJ1.zip

1 row selected.

SQL> with CPR_TBL as (select 'AJ1.zip' CPR_ID from dual)
  2  select  distinct(CPR_ID)  from CPR_TBL where ( CPR_ID like 'J%.zip' or  CPR_ID like 'M%.zip')
  3  /

no rows selected

And they don't return the same column.

Regards
Michel
Re: Use of REGEXP_LIKE [message #338548 is a reply to message #338540] Tue, 05 August 2008 05:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for pointing out the mistake.
I need to know which would be feasible in 10g. Will both queries give same performance?



select  distinct(CPR_ID)  from CPR_TBL where REGEXP_LIKE(CPR_ID,'^[J|M]*.zip$')
/





select  distinct(CPR_ID)  from CPR_TBL where ( CPR_ID like 'J%.zip' or  CPR_ID like 'M%.zip')
/



[Updated on: Tue, 05 August 2008 05:46]

Report message to a moderator

Re: Use of REGEXP_LIKE [message #338568 is a reply to message #338548] Tue, 05 August 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The classical one is better as it allows to use standard index.
In addition regexp functions have very bad performances in 10g.

Regards
Michel
Re: Use of REGEXP_LIKE [message #338585 is a reply to message #338568] Tue, 05 August 2008 07:07 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for the update.Lack of 10g environment compelled me
to put my question here.Can I get any test environment (10g) through web to test some sample sql

Thank you very much


Regards,
Oli
Re: Use of REGEXP_LIKE [message #338587 is a reply to message #338585] Tue, 05 August 2008 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://apex.oracle.com/i/index.html

Create an account.

Regards
Michel
Re: Use of REGEXP_LIKE [message #338600 is a reply to message #338587] Tue, 05 August 2008 07:26 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thank you very much Smile


Regards,
Oli
Previous Topic: Lock Rows
Next Topic: unable to view procedure
Goto Forum:
  


Current Time: Thu Dec 08 08:35:18 CST 2016

Total time taken to generate the page: 0.05953 seconds