Oracle Regular Expression using instead of INSTR function

Question

i keep data on table rows as followed like this;

t_course

+------+------------------------------------------+
| sid  |  courses                                 |
+------+------------------------------------------+
|  1   |  cs101.math102.ns202-2.phy104            |
+------+------------------------------------------+
|  2   |  cs101.math201.ens202-1.phy104-10.chm105 |
+------+------------------------------------------+
|  3   |  cs101.ns202-2.math201.ens202-1.phy104   |
+------+------------------------------------------+

Now, i want to take the sum of courses mentioned ns202 and ens202 in same time. Normally it should only brings record which id is 3, it brings all of the records (because of instr). i have used many methods for this, but it doesn't work. For example;

select count(*) from 
   t_course 
      where 
            instr(courses, 'ns202')  > 0 
        and instr(courses, 'ens202') > 0;

Above code doesn't work properly because it takes ns202 but ens202 contains ns202 in itself.

I tried using regular expressions, i converted all course to row (split) but this has both broke working logic and slowed down.

How can i do this with regular expressions instead of instr according to begin withs (for example ns202%) logic? (Begining with ns202 first or after dot)

@vkp, Yes, i want to only sum of records which contains both ns202 and ens202. — Goran Zooferic
– Goran Zooferic, Commented Dec 28, 2016 at 21:28
For example there is only total one record according to this case(both ns202 and ens202) in my table. — Goran Zooferic
– Goran Zooferic, Commented Dec 28, 2016 at 21:32
Why is your input data in that format in the first place? First Normal Form is violated - the most basic requirement of relational tables. The reason you even have to think about such things (instead of a much, much simpler query) is that a single "value" contains multiple "actual values". — user5683823
– user5683823, Commented Dec 28, 2016 at 21:39

Vamsi Prabhala · Accepted Answer · 2016-12-28 21:41:15Z

2

You can use regexp_like with word boundaries to get rows which have both ns202 and ens_202. Normally you would use \b for word-boundaries. As Oracle doesn't support it, the alternate is to use (\s|\W) with start ^ and end $ anchors.

\s - space character, \W - non word character. Add more characters as needed, as word-boundaries based on your requirements.

select *
from t_course   
where regexp_like(courses,'(^|\s|\W)ns202(\s|\W|$)') 
and regexp_like(courses,'(^|\s|\W)ens202(\s|\W|$)')

edited Dec 28, 2016 at 21:41

answered Dec 28, 2016 at 21:31

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2016-12-28 21:36:02Z

1

You will have the same problem with ens202, by the way - what if there is also cens202or tens202?

You can solve your problem with regular expressions. You can also solve it with the LIKE operator:

select <whatever>
from   <table or tables>
where (courses like 'ns202%' or courses like '%.ns202%')
  and (courses like 'ens202%' or courses like '%.ens202%')

You can test both approaches to see which works best for your data.

answered Dec 28, 2016 at 21:36

user5683823

Collectives™ on Stack Overflow

Oracle Regular Expression using instead of INSTR function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related