How to count sub strings and group by substring

Question

I'm trying to count substring but it fails. I am using the below query:

I tried the count by sub string method, but it failed with the below error:

inconsistent datatypes: expected - got CLOB
 00932. 00000 -  "inconsistent datatypes: expected %s got %s"

select substr(substr(reponse,INSTR(reponse,'giftType":"')-1),13,1) reponse ,  
       count(*) 
from app_request_log
where uri='/mobile-app'
and user_name='123467654'
and creation_Date >='01-JUL-18'
group by substr(substr(reponse,INSTR(reponse,'giftType":"')-1),13,1) ;

I need the output to be like:

reponse count
1         5
2         3
3         9

This is the column data, bearing in mind that I can't separate value into separate columns:

{"eCode":0,"eDesc":"Success","correlationId":"x","errorMessage":null,"giftSeqID":null,"giftEnddate":"0:00:00%2B0200","giftStatus":"2","giftStartdate":"0:00:00%2B0200","giftType":"3"}

Should we assume from the error message that the DBMS is Oracle? — Joakim Danielson, Jul 05 '19 at 08:31
does it work without the count? Have you tried putting the substring in a sub query and only have the count and group by in the outer query? — Letimogo, Jul 05 '19 at 08:39
Possible duplicate of [Group by alias (Oracle)](https://stackoverflow.com/questions/268429/group-by-alias-oracle) — Joakim Danielson, Jul 05 '19 at 08:41
I suppose that with CLOBs you must use `DBMS_LOB.INSTR` and `DBMS_LOB.SUBSTR`. https://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89852/dbms_lob.htm — Thorsten Kettner, Jul 05 '19 at 08:46
As mentioned above use functions from dbms_lob package. [Here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=77e7ab007bcb3291c85faf89c4e6177e) is an example with non-working and working query. — Ponder Stibbons, Jul 05 '19 at 10:23

How to count sub strings and group by substring

0 Answers0