process scanned value before using it as a ref key

[MyPill]

Hi,

I am working on an app to record transfers of pills. I use scanner to read barcodes from pill's packages to identify them. I have table PILL with unique codes of the types of pills (one type = one code) and table BATCH where one type of pill can have more different production batches with different expiration dates (one batch = one code; one pill type can have multiple batch codes).

Now - some packages have EAN13, but some others have 2d matrix code that strores more information about the package: type (the same value as what used to be in EAN13), batch and expiration date. I need to scan any type of package and be able to identify its type and find it in the PILL table. If I scan EAN13 it immediatelly finds the correct type, that's OK. But if I scan 2d matrix code I need to extract the type code first and then find that code in the PILL table. But how?

The solution is not to have all 2d matric codes in the PILL table, as the PILL table should have only one record for different type of pill, not one row for different batch of the same type of pill.

So I am looking for a way to

1. scan the code
2. try to find it in PILL table
3. if the code is in the PILL table, then use it as a REF key
4. if not then then try to extract a type code from the scanned string (maybe it is a 2d matrix code) that matches any code in the PILL table; if it succeeds then use it as a REF key
5. if the previous false then return nothing/null

Any idea? Thank you in advance!