I want to identify the number of words occurring after a comma, in a full name field in Oracle database table.
The name field contains format of "LAST, FIRST MIDDLE" Some names may have up to 4 to 5 names, such as "DOE, JOHN A B"
For example, if the Name field = 'WILLIAMS JR, HANK' it would output 1 (for 1 word occurring after the comma.
If the Name field = 'DOE, JOHN A B' i want it to output 3.
I would like to use a regexp_count function to determine this count. I am using the following code to identify how many words exist in the field and would like to modify it to include this functionality:
REGEXP_COUNT(REPLACE(fieldname, ',',', '), '[^]+')
It would likely have to remove the replace function in order to find the comma, but this was the best I could do so far.
Help is much appreciated!
I have updated the code as follows, which appears to be working as desired:
REGEXP_COUNT(fieldname, '[^ ]+', (INSTR(fieldname, ',')+1))