Gyanendra Kumar Gyanendra Kumar - 1 month ago 7
SQL Question

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.

vkp vkp
Answer

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
Comments