Extract from string

[gromboy]

Hi,
I have a sql server db which has a field in a table called DRUGDETS. In this field is the following:
Drugname Drugstrength Unit of measure, Route, Vehicle

Example
CISPLATIN 50 mg, IV Infusion, Saline 0.9%

I want to be able to report on the Drugname and route
eg CISPLATIN IV INFUSION

There might be different routes for each drug so what I'm trying to do is report on all the CISPLATIN IV INFUSION, then all the CISPLATIN IV BOLUS as seperate entities. The drugname might be 2 words, the dose is always a number.

Any help greatly appreciated as ever !
Steve

 

[gromboy]

I'm afraid not... If its an IV BOLUS then there wont be a vehicle.... but they may setup other routes which dont have a vehicle...

 

[lbass]

Well, what exactly do you want to show on your report? What is the final display? If you just need to count instances of drugs, for example, you could use running totals that evaluate the presence of a drug in the field, without actually breaking each drug out for display.

-LB

 

[IanWaterman]

Again not very efficient but you could use a massive replace formula to remove amounts and replace with standard entry

@replace
replace(replace({yourfield}, "mg,", " , "),"mmols"," , ")

Keep building for all amount types. If you add another amount type in the future you will need to include in the replace formula.

Then use LBs formula changed as folows

stringvar x := {@replace};
stringvar y := left(x,instrrev(x," , ")+1);
stringvar array z := split(y," , ");
stringvar k;
numbervar i;
numbervar j := ubound(z);

for i := 1 to j do(
k := k + split(z," ")[1]+" "
);
k

 

[gromboy]

What i want to do is count every product which is made by pharmacy

Single drugs, and combinations.

I've been trying to work out what could be used identify the drugnames only... but I dont think its possible.
I've also looked at other fields in the table to see if we can use them as a reference but can't seem to find anything.

I think I may have to give up on this one...
Thanks to you both for your help
Steve

 

[gromboy]

Hang on...what about removing the numbers..this would make them the same... I assume that can be done ?
Steve