Remove certain characters from query field using string functions

[SHAHEEDFAZAL]

Hi,



I have the following entries in my column:

Full Description
Abelcet vial 5mg/ml 20ml 10 pack
Abilify dispersible tablet 10mg 28 pack
Abilify dispersible tablet 15mg 28 pack
Abilify oral solution 1mg/ml 150ml
Abilify solution for injection 7.5mg/ml 1.3ml
Abilify tablets 10mg 28 pack
Abilify tablets 15mg 28 pack
Abilify tablets 30mg 28 pack
Abilify tablets 5mg 28 pack
Accolate tablets 20mg 56 pack
Accupro tablets 10mg 28 pack
Accupro tablets 20mg 28 pack
Accupro tablets 40mg 28 pack
Accupro tablets 5mg 28 pack
Accuretic tablets 28 pack

I want to use expression builder to remove anything that has "[number] pack". i.e convert Accuretic tablets 28 pack to Accuretic tablets but leave Abilify solution for injection 7.5mg/ml 1.3ml as is.

I have been trying string functions to do this all afternoon but somehow I just can't get it to work.

Your help would be much appreciated.

Kind regards,

Shaheed Fazal

[jzwp11]

I believe the following query will return the modified text you desire based on the data you posted (you will have to substitute your own table and field names)

SELECT tblMeds.ID, tblMeds.txtMedication,IIF( instr(1,txtMedication,"pack")>0,left(txtMedication,instr(1,txtMedication,"pack")-5),txtMedication ) as modifiedtext
FROM tblMeds;

If you want to actually change the text in the field, you will have to use the expression above in an update query.

[SHAHEEDFAZAL]

Thanks for your help. This did not quite work as there are some instances where a medicine may be a blister pack. For example:

Zaditen tablets blister pack 1mg 60 pack

Using the formula you suggested, I get:

Zaditen tablets blis

So ideally, the formula needs to be one whereby if the field ends in pack and is preceded by a number, remove the number and the word pack.

[jzwp11]

This might work:

SELECT tblMeds.ID, tblMeds.txtMedication,IIf(instrrev(txtMedication,"pack")>0,left(txtMedication,instrrev(txtMedication,"pack")-4),txtMedication) AS modifiedtext2
FROM tblMeds;

[SHAHEEDFAZAL]

Almost there. I picked up an anomaly:

Actilyse injection pack 20mg

becomes

Actilyse injecti

So I think we need to put a condition saying that if pack is followed by number then also ignore.

Also if you have time it would be great if you could explain what is going on so I can understand how to do this type of thing in the future.

[jzwp11]

Without seeing your data, it is hard to make the query work for every variation in your data. You will want to explore the IIF() function to add conditions to the expression I provided previously.