Home » SQL & PL/SQL » SQL & PL/SQL » filter based on extracting string (12c)
filter based on extracting string [message #674090] Thu, 03 January 2019 04:28 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i have posted a query in oracle forums for extracting data based on filter but i did not get the exact output that i want. I need your help.

https://community.oracle.com/message/15027343#15027343

[code]

i have to filter out fields based on following conditions.
like i need only AG1 , AG2,AG3 ( They can be both lower and upper case)
sometime there are cases like 'ag 1' and 'ag-1' or 'ag2' as well but i dont want something like 'against' or 'aggregate'
in short only AG Series.Anything before AG should also consider the criteria s.




 

CREATE TABLE OT_REMARK ( OT_REMARKS VARCHAR2(24))

 

INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('AG');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('AG1');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('AG2');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('AG-1');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('AG 1');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('Ag 1');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('Ag2');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('Against');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('The Ag2');
INSERT INTO OT_REMARK(OT_REMARKS) VALUES ('Bag)');

 

--desired outuput is

 

AG
AG1
AG2
AG-1
AG 1
Ag 1
Ag2
Ag2


 


Re: filter based on extracting string [message #674094 is a reply to message #674090] Thu, 03 January 2019 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you have anything between the AG and the number other than hyphen and space?
Re: filter based on extracting string [message #674096 is a reply to message #674090] Thu, 03 January 2019 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given your remark to mathguy, you accept anything ending by a space or a punctuation before your searched string, in this case, just modify his regular expression to add this condition:
SQL> select ot_remarks
  2  from   ot_remark
  3  where  regexp_like(ot_remarks, '^(.*[[:space:][:punct:]])?ag[[:space:][:punct:]]?[[:digit:]]*$', 'i')
  4  /
OT_REMARKS
------------------------
AG
AG1
AG2
AG-1
AG 1
Ag 1
Ag2
The Ag2

[Updated on: Thu, 03 January 2019 05:36]

Report message to a moderator

Re: filter based on extracting string [message #674097 is a reply to message #674094] Thu, 03 January 2019 05:51 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
no it is just one word.
Re: filter based on extracting string [message #674098 is a reply to message #674096] Thu, 03 January 2019 05:53 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Thanks Michel, it works , i was trying to add a 'OR' condition like below.


select ot_remarks
from  ot_remark
where  regexp_like(ot_remarks,'((([[:space:]])+ag)|^ag)+[[:space:]]*+[[:punct:]]*+[[:digit:]]','i')

Re: filter based on extracting string [message #674099 is a reply to message #674097] Thu, 03 January 2019 05:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

m.abdulhaq wrote on Thu, 03 January 2019 12:51
no it is just one word.
Do you mean that "And the Ag2" should be rejected?

Previous Topic: Substring and Instring in combination
Next Topic: Regarding Updatable view - Please assist
Goto Forum:
  


Current Time: Thu Mar 28 10:09:36 CDT 2024