CODEWITHSUNDEEP
sqloracle10g
Gyanendra Kumar
Gyanendra Kumar

Reputation: 39

Return count of records in each row SQL

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.

Upvotes: 0

Views: 52

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Oracle has the very convenient regexp_count(). So:

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

Upvotes: 2

Related Questions