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

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

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.

Collectives™ on Stack Overflow

Return count of records in each row SQL

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related