Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression (Oracle - 10.2.0.4.0 - 64bi. OS Windows Xp)
Regular Expression [message #362106] Sun, 30 November 2008 23:41 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hello every one.

I am new to REGEXP in oracle and I have situation to handle. I have certain set of string which I need to find position of AND operator.

There are few Cases

1. 'B= 10 AND C=''TRIVENDRA'''
2. 'B= ''10 AND'' AND C=''TRIVENDRA'''
3. 'BRAND=''TRIVENDRA AND'' AND B=9'

In all the cases I need position of AND operator, I have tried a query that is successful in 1 but fails for 2 and 3

-- Case 1 
SELECT REGEXP_INSTR ('B= 10 AND C=''TRIVENDRA''', '(^|)[''][[url=/wiki/:space:]:space:[/url][AND]{3}]+|(^|).[AND]{3}.+') strpos
  FROM DUAL

    STRPOS
----------
         6
-- Case 2
SELECT REGEXP_INSTR ('B= ''10 AND'' AND C=''TRIVENDRA''', '(^|)[''][[url=/wiki/:space:]:space:[/url][AND]{3}]+|(^|).[AND]{3}.+') strpos
  FROM DUAL;

    STRPOS
----------
         7

-- Case 3
SELECT REGEXP_INSTR ('BRAND=''TRIVENDRA AND'' AND B=9', '(^|)[''][[url=/wiki/:space:]:space:[/url][AND]{3}]+|(^|).[AND]{3}.+') strpos
  FROM DUAL;

    STRPOS
----------
         2


Can any one suggest me how to handle such condition?

After the formatting the regular expression condition is changed.

Please use this condition '(^|)[''][[ [:space:] ][AND]{3}]+|(^|).[AND]{3}.+'

Thanks
Trivendra
Re: Regular Expression [message #362113 is a reply to message #362106] Mon, 01 December 2008 00:38 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Aren't regular expressions an overkill here? How about INSTR?
select instr(your_string, ' AND ') position from ...

As of "wiki" infiltrated into your code, well, take a look at this thread. I bet you'll find it helpful.
Re: Regular Expression [message #362211 is a reply to message #362106] Mon, 01 December 2008 05:51 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks for sugestions, But here it will not help, I need AND operator position in these strings.

Re: Regular Expression [message #362219 is a reply to message #362211] Mon, 01 December 2008 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you need to handle this case:

4. 'BRAND=''TRIVENDRA AND CO'' AND B=9'
Re: Regular Expression [message #362221 is a reply to message #362106] Mon, 01 December 2008 06:20 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Yes.. You are right. but the problem is that I need a expression which satisfy all three cases.
Re: Regular Expression [message #362227 is a reply to message #362221] Mon, 01 December 2008 06:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is the condition always going to be an equality check ie will it always have an = in it. If not, can you provide a list of the conditions you wish to check for.
Re: Regular Expression [message #362233 is a reply to message #362227] Mon, 01 December 2008 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming it will always be an equality check, you could use this:
with src as (select 'B= 10 AND C=''TRIVENDRA''' col_1 from dual union all
             select 'B= ''10 AND'' AND C=''TRIVENDRA''' from dual union all
             select 'BRAND=''TRIVENDRA AND'' AND B=9' from dual union all
             select 'BRAND=''TRIVENDRA AND CO'' AND B=9' from dual)
select col_1
      ,regexp_substr(col_1,' AND[^'||chr(39)||']*=.*$')
from src;


This looks for the string ' AND' followed by any number of non-quote characters (to exclude the possility of the ANY being part of a string, followed by an =, and then anything you like up to the end of the line.
Re: Regular Expression [message #362236 is a reply to message #362106] Mon, 01 December 2008 06:59 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
This is Fantastic.

Once again you have provided me accurate solution to my problem.

Yes .. In brief, this is a Condition which comes after WHERE clause in a SQL statement, i.e. Insert, Delete or Update.

So "=" will always come after AND operator.

This is Great and best quality of Solution.

Thanks
Previous Topic: Getting error 'invalid column name' When filling and opening ref cursor or oracle object.
Next Topic: inserting the data in the deleted part of a table
Goto Forum:
  


Current Time: Mon Dec 05 06:32:32 CST 2016

Total time taken to generate the page: 0.09660 seconds