Return count of records in each row SQL

Question

I have one table like this.

 SQL> SELECT * FROM FRUIT;

 F_NAME
 ----------
 APPLE
 PPPALEP
 APLEE
 PPAALLEEPP
 ornPpfpP    
 PPdhppPP

Above one is my source table and I want to below output.If i am giving 'P' in multiform like including capital and small both. I want to count only 'P' from each row.

OUTPUT
------
F_NAME      COUNT
------      -----
APPLE         2
PPPALEP       4
APLEE         1
PPAALLEEPP    4
ornPpfpP      4
PPdhppPP      6

Thanks in advance.

Gordon Linoff · Accepted Answer · 2016-10-11 19:43:55Z

2

Oracle has the very convenient regexp_count(). So:

select f_name, regexp_count(f_name, 'P') as cnt
from fruit;

answered Oct 11, 2016 at 19:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Vamsi Prabhala Over a year ago

i didn't know about this function. thanks. i see it was introduced in oracle 11g.

Gyanendra Kumar Over a year ago

I have tried a lot using regexp_count,but it is not working in oracle 10g.

Vamsi Prabhala · Accepted Answer · 2016-10-11 20:53:06Z

0

You can count the number of occurrences by replacing P with blanks and subtracting the length of the replaced string from the original string.

select f_name,length(f_name)-length(replace(f_name,'P','')) cnt
from fruit

Edit: Per OP's comment, to count both P and p, use upper or lower when replacing the character with an empty string.

select f_name,length(f_name)-length(replace(upper(f_name),'P','')) cnt
from fruit

edited Oct 11, 2016 at 20:53

answered Oct 11, 2016 at 19:43

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

3 Comments

Gyanendra Kumar Over a year ago

This query is working fine for only count capital 'P' .If i am giving 'P' in multiform like including capital and small both. I want to count both 'P'(like capital and small) from each row.Then what is the logic.

Gyanendra Kumar Over a year ago

Thanks in advance @vkp

Gyanendra Kumar Over a year ago

Now i got my answer.Thanks @vkp

Collectives™ on Stack Overflow

Return count of records in each row SQL

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related