Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE function (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
REGEXP_LIKE function [message #358404] Tue, 11 November 2008 02:31 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I have a requirement which I feel would be easy with REGEXP_LIKE function.


CREATE TABLE TEST12
(
  TESTCOL  VARCHAR2(50);
)

insert into test12 values ('XYZ (SIT)');
insert into test12 values ('XYZ (DIT-SIT)');
insert into test12 values ('XYZ (DIT)');
insert into test12 values ('XYZ (UAT) old R1.0');
insert into test12 values ('XYZ (SIT-UAT) old R1.0');
insert into test12 values ('XYZ (SIT-UAT) old R2.0');
insert into test12 values ('XYZ (SIT) old R1.0');
insert into test12 values ('XYZ (SIT) old R2.0');
insert into test12 values ('XYZ (PSUP-PROD)');
insert into test12 values ('XYZ (UAT)');
insert into test12 values ('XYZ (SIT-UAT)');

COMMIT;


Case 1
'XYZ (DIT)'

Case 2
'XYZ (DIT)', 'XYZ (DIT-SIT)', 'XYZ (SIT)', 'XYZ (SIT) old R1.0', 'XYZ (SIT) old R2.0'

Case 3
'XYZ (DIT)', 'XYZ (DIT-SIT)', 'XYZ (SIT)', 'XYZ (SIT) old R1.0', 'XYZ (UAT) old R1.0',
'XYZ (SIT-UAT) old R1.0', 'XYZ (SIT-UAT) old R2.0', 'XYZ (PSUP-PROD)', 'XYZ (SIT-UAT)'


I have different cases, which I can use multiple LIKE cases and implement as

Case 2 can be implemented as

SELECT * FROM TEST12
WHERE (testcol LIKE 'XYZ (DIT)%' OR testcol LIKE 'XYZ (SIT)%' OR
testcol LIKE 'XYZ (DIT-SIT)')


How can the above cases be achieved through REGEXP_LIKE with minimum OR cases...



Re: REGEXP_LIKE function [message #358408 is a reply to message #358404] Tue, 11 November 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what did you try so far?
And what is the rule for case 3?
And case 1? Is it just =?

In short what do you want?

Regards
Michel

[Updated on: Tue, 11 November 2008 02:53]

Report message to a moderator

Re: REGEXP_LIKE function [message #358417 is a reply to message #358404] Tue, 11 November 2008 03:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Sorry for not being specific. There might be other rows in the table but I need the rows for which testcol LIKE given below -

Case 1
testcol IN ('XYZ (DIT)')

Case 2
testcol IN (
'XYZ (DIT)', 'XYZ (DIT-SIT)', 'XYZ (SIT)', 'XYZ (SIT) old R1.0', 'XYZ (SIT) old R2.0')

Case 3
testcol IN ('XYZ (DIT)', 'XYZ (DIT-SIT)', 'XYZ (SIT)', 'XYZ (SIT) old R1.0', 'XYZ (UAT) old R1.0',
'XYZ (SIT-UAT) old R1.0', 'XYZ (SIT-UAT) old R2.0', 'XYZ (PSUP-PROD)', 'XYZ (SIT-UAT)')

I hope it is more clear now....
Re: REGEXP_LIKE function [message #358418 is a reply to message #358417] Tue, 11 November 2008 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're using 10g, then your current solution will probably be quicker than a Regexp based one.
Re: REGEXP_LIKE function [message #358419 is a reply to message #358404] Tue, 11 November 2008 03:35 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

hmmm ok...

So the condition would be like
(Case = 1 AND testcol LIKE 'XYZ GO (DIT)%')
                                OR
                                (Case = 2 AND (testcol LIKE 'XYZ GO (DIT)%'
                                                   OR testcol LIKE 'XYZ GO (DIT-SIT)%'
                                                   OR testcol LIKE 'XYZ GO (SIT)%'
                                                      )  
                                )
                                OR
                                (Case = 3 AND (testcol LIKE 'XYZ GO (DIT)%'
                                                         OR testcol LIKE 'XYZ GO (DIT-SIT)%'
                                                         OR testcol LIKE 'XYZ GO (SIT)%'
                                                         OR testcol LIKE 'XYZ GO (UAT)%'
                                                         OR testcol LIKE 'XYZ GO (DIT-SIT-UAT)%'
                                                           )
                                )
                               )     


But for knowledge sake, how it is achievable through REGEXP_LIKE?

[Updated on: Tue, 11 November 2008 03:36]

Report message to a moderator

Re: REGEXP_LIKE function [message #358420 is a reply to message #358419] Tue, 11 November 2008 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 11 November 2008 09:51
And what did you try so far?
Regards
Michel

Re: REGEXP_LIKE function [message #358422 is a reply to message #358419] Tue, 11 November 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from test12
where     ( :c = 1 and testcol LIKE 'XYZ (DIT)%' )
       or ( :c = 2 and regexp_like (testcol, '^XYZ \((DIT|SIT|DIT-SIT)\)') )
       or ( :c = 3 and regexp_like (testcol, '^XYZ \((DIT|SIT|UAT|DIT-SIT|DIT-SIT-UAT)\)') )
/

This is the exact transcription of your previous condition but I bet this is not what you want.

Regards
Michel
Re: REGEXP_LIKE function [message #358429 is a reply to message #358422] Tue, 11 November 2008 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmmm..
I make the comdition more like this:
select * from test12
where regexp_like (testcol,'XYZ \(DIT\)') -- case 1
or   (regexp_like (testcol,'XYZ \(([DS]IT|DIT\-SIT)\)$') or regexp_like (testcol,'XYZ \(SIT\) old R[12].0$')) -- case 2
or   (regexp_like(testcol,'XYZ \(([DS]IT|DIT\-SIT|SIT\-UAT|PSUP\-PROD)\)$') or regexp_like(testcol,'XYZ \((SIT|UAT|SIT\-UAT)\) old R[12].0$')) -- case 3
;
, as this includes checks for the trailing "old R1.0" strings.

however, this is mere nit picking - I too strongly suspect that this is not what is actually wanted.
Re: REGEXP_LIKE function [message #358432 is a reply to message #358404] Tue, 11 November 2008 05:10 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks.. Well it may work but it looks using OR condition with LIKE is much easier to follow as the possible values are not more...

Quote:

Michel Cadot wrote on Tue, 11 November 2008 09:51
And what did you try so far?
Regards
Michel





Well, I was facing problem with '(' as it was giving error Sad
but got it that it can be ignored with '\'....

Thanks anyways...
Previous Topic: default value and not null constraint
Next Topic: Merge TWO or more Result Rows in ONE
Goto Forum:
  


Current Time: Thu Dec 08 03:56:15 CST 2016

Total time taken to generate the page: 0.06510 seconds