filter based on extracting string [message #674090] |
Thu, 03 January 2019 04:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c70e51380f6e8af6765f6e8fede51587?s=64&d=mm&r=g) |
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 #674096 is a reply to message #674090] |
Thu, 03 January 2019 05:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 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
|
|
|
|
|
|