Query for search and count words

[kokiri]

Please help!

These are samples of medications for one patient, varchar2(4000), and I want to be able to count how many times 'qd' are used.

meds
-----
Thiamine 100mg qd Gabapentin 1200mg qAM Gabapentin 1800mg qPM Zantac 300mg qd Lopressor 50mg bid Finasteride 5mg qd
Niacin 1000mg qd Ferrous Gluconate 325mg qd Vicodin 5/500 1 Tab q 4hrs prn Cyclobenzaprine 10mg TID Diclofenac 75mg prn
Citalopram 40mg qd Terazosin 5mg qd Aspirin 81mg qd Folic acid 1mg qd Digoxin 0.125mg qd Lisinopril 2.5mg qd

Thanks.

Kokiri

 

[gunjan14]

Hello Kokiri,

How about this

select (a.col1 - b.col1)/2
from   (select length(meds) col1
        from   table_name
       ) a,
       select length(replace(meds,'qd')) col1
        from   table_name
       ) b
;
I am assuming your column_name is meds

let us know if this suffixes your need

Regards,
Gunjan

 

[gunjan14]

Oops, no need to write inline view. you can use like this

select (length(meds)-length(replace(meds,'qd')))/2
from   table

Regards,
Gunjan

 

[kokiri]

Gunjan,

That is what I'm looking for. Thank for your prompt response.

Kokiri