Link to home
Start Free TrialLog in
Avatar of Jonathan CMCH
Jonathan CMCH

asked on

MySQL Query - Searching for a string

I'm working on an application for a local pharmacy and I'm using mysql as the back end DB.  I'm using a query to search a table of drugs based on what is keyed in the program side.  i.e., if they want to search for a drug with an NDC # of 517460125, they type in the series of numbers and the program runs this sql query...

select
itemnumber, barcodeintray, name, ivm3ndc, ivm3_nivndc,
ndc1, ndc2, ndc3, ndc4, ndc5, ndc6, ndc7, ndc8, ndc9,
ndc10, quantity

from
pharmacy_box.items

where
items.itemnumber like '%517460125%' or
items.name like '%517460125%' or
items.ivm3ndc like '%517460125%' or
items.ivm3_nivndc like '%517460125%' or
items.ndc1 like '%517460125%' or
items.ndc2 like '%517460125%' or
items.ndc3 like '%517460125%' or
items.ndc4 like '%517460125%' or
items.ndc5 like '%517460125%' or
items.ndc6 like '%517460125%' or
items.ndc7 like '%517460125%' or
items.ndc8 like '%517460125%' or
items.ndc9 like '%517460125%' or
items.ndc10 like '%517460125%'

Of course, the numbers would be replaced by a variable in the code, but that's how the query is structured.  This will search the various columns in the table and report back if found a column with such data.  Simple query.

Here's my dilemma.

Some NDC #'s can contain more characters than what is entered into the DB.  So, for example, a tech scans this particular drug and it sees the NDC number 0010517460125.  I'm trying to figure out a way to restructure the query to search, not only, the scanned NDC number with what's in the DB, but also what's in the DB against what is scanned.

I hope that makes sense.
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

So ... longer numbers in the NDC fields should be disallowed?  The record with 0010517460125 should not be returned?  On those fields you want an exact match?

Or am I misunderstanding?
Avatar of lisfolks
lisfolks

Hmm... the way I'm taking your question is that a person can type an entry into the program. The program then puts that entry (say, 517460125) into the query to be sent to the DB.

However, the code can ALSO be SCANNED into the program. In this case, the code may be longer than what the DB contains in the pharmacy_box.items table. So, for example, the table might contain 517460125, found easily when typed in, but the scan might produce 0010517460125 - which doesn't actually exist in the table.

So, your query will find '%517460125%', but wouldn't find '%0010517460125%'.

Yes???

If so, then will the code in the DB consistently contain a portion of the scanned code's characters? Meaning, using the example above, the rightmost nine characters of the scanned code would match the rightmost nine characters of the code in the DB table?

If some subset of the scanned characters will always match the characters actually stored in the table, then use a 'substring' statement to select only the portion of the scanned code that will possibly match.
Avatar of Jonathan CMCH

ASKER

Thanks Daniel and Lisfolks,

continuing with the example above, the DB would contain "517460125".  The scanned NDC number could be longer (00105174601251) or shorter (746012)...our health information system vendor is nuts.  

The way the sql query works now is that it's only checking if the scanned string is within the string on the DB.  What I'm looking for is to check if the DB string is contained with in the scanned string or if the scanned string is within the DB string.

To answer the latter part of your question, lisfolks, the characters could be shifted around.  One day, the code in the DB could be 517460125, the next 51746012591, then the next 0517460125920.  Crazy?...yeah, I know.  Gotta love the healthcare system.
Again, is this query being called within a program/application or is it being used within a stored procedure? If a program, what language (eg, Java, C#.NET, ...?), or if a stored procedure, what database (eg, Oracle PL/SQL, SQL Server, MySQL, ...?) Different platforms have different capabilities, so that might help us advise you.
I'm writing the program in VB .net.  It's just executing the previous query.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of lisfolks
lisfolks

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Lisfolks, I'll take a look at that.  I think you got it.
Just a fyi for those who come across this post, my final query looks like this.  Again, the numbers in semi-quotes is actually a variable in a program and are not static.

select
itemnumber, barcodeintray, name, ivm3ndc, ivm3_nivndc,
ndc1, ndc2, ndc3, ndc4, ndc5, ndc6, ndc7, ndc8, ndc9,
ndc10, quantity

from
pharmacy_box.items

where
instr(items.itemnumber, '1517460125') > 0 or
instr('1517460125', items.itemnumber) > 0 and
items.itemnumber <> 0 or

instr(items.name, '1517460125') > 0 or
instr('1517460125', items.name) > 0 and
items.name <> 0 or

instr(items.ivm3ndc, '1517460125') > 0 or
instr('1517460125', items.ivm3ndc) > 0 and
items.ivm3ndc <> 0 or

instr(items.ivm3_nivndc, '1517460125') > 0 or
instr('1517460125', items.ivm3_nivndc) > 0 and
items.ivm3_nivndc <> 0 or

instr(items.ndc1, '1517460125') > 0 or
instr('1517460125', items.ndc1) > 0 and
items.ndc1 <> 0 or

instr(items.ndc2, '1517460125') > 0 or
instr('1517460125', items.ndc2) > 0 and
items.ndc2 <> 0 or

instr(items.ndc3, '1517460125') > 0 or
instr('1517460125', items.ndc3) > 0 and
items.ndc3 <> 0 or

instr(items.ndc4, '1517460125') > 0 or
instr('1517460125', items.ndc4) > 0 and
items.ndc4 <> 0 or

instr(items.ndc5, '1517460125') > 0 or
instr('1517460125', items.ndc5) > 0 and
items.ndc5 <> 0 or

instr(items.ndc6, '1517460125') > 0 or
instr('1517460125', items.ndc6) > 0 and
items.ndc6 <> 0 or

instr(items.ndc7, '1517460125') > 0 or
instr('1517460125', items.ndc7) > 0 and
items.ndc7 <> 0 or

instr(items.ndc8, '1517460125') > 0 or
instr('1517460125', items.ndc8) > 0 and
items.ndc8 <> 0 or

instr(items.ndc9, '1517460125') > 0 or
instr('1517460125', items.ndc9) > 0 and
items.ndc9 <> 0 or

instr(items.ndc10, '1517460125') > 0 or
instr('1517460125', items.ndc10) > 0 and
items.ndc10 <> 0
;