Home » SQL & PL/SQL » SQL & PL/SQL » Count occurrences of String in field (Oracle 11g, Win 10)
Count occurrences of String in field [message #668468] Mon, 26 February 2018 15:29 Go to next message
rajivn786
Messages: 146
Registered: January 2010
Senior Member
Hi,

I need count the occurrences of male and female in string, but I am getting incorrect results for male as it's counting female as well due to "male" string in word female.

 Select  REGEXP_COUNT(' 67 y.o. male PMHx stated below presenting for followup of HTN. Patient seen for annual exam, dx with HTN. Started on lisinopril, tolerating well and female sex partner', 'male') from dual 

Instead of getting output as 1 , I am getting 2.

Actually we have clob field, but I just trimmed it to fit here.
Re: Count occurrences of String in field [message #668473 is a reply to message #668468] Mon, 26 February 2018 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65953
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Instead of getting output as 1 , I am getting 2.

Result is correct:

' 67 y.o. male PMHx stated below presenting for followup of HTN. Patient seen for annual exam, dx with HTN. Started on lisinopril, tolerating well and female sex partner'

If you want whole words only you can use something like:
SQL> Select REGEXP_COUNT(
  2           ' 67 y.o. male PMHx stated below presenting for followup of HTN. Patient seen for annual exam, dx with HTN. Started on lisinopril, tolerating well and female sex partner',
  3           '(^|\W)male($|\W)')
  4         res
  5  from dual;
       RES
----------
         1
Or better use Oracle Text extension which also allows you to index the words and so is more efficient to retrieve them.

Re: Count occurrences of String in field [message #668487 is a reply to message #668473] Tue, 27 February 2018 10:52 Go to previous message
rajivn786
Messages: 146
Registered: January 2010
Senior Member
Thanks!
Previous Topic: Table Archive and Purge
Next Topic: Compare row & column data
Goto Forum:
  


Current Time: Wed Nov 14 04:58:42 CST 2018