Link to home
Create AccountLog in
Avatar of Jonathan CMCH
Jonathan CMCH

asked on

MySQL String Search

I'm trying to locate an item stored in a mysql database using VB .net running a simple query.  The issue is this:

The program ask a user to type in a string to use during the query of the mysql DB.  For example, the user can put in 512 and the query will, using %512%, locate everything with that string.  The problem I have is when the user enters something like 1512.  The Query skips the columns that do not match.  Here is the 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 '%517460%' or
items.name like '%517460%' or
items.ivm3ndc like '%517460%' or
items.ivm3_nivndc like '%517460%' or
items.ndc1 like '%517460%' or
items.ndc2 like '%517460%' or
items.ndc3 like '%517460%' or
items.ndc4 like '%517460%' or
items.ndc5 like '%517460%' or
items.ndc6 like '%517460%' or
items.ndc7 like '%517460%' or
items.ndc8 like '%517460%' or
items.ndc9 like '%517460%' or
items.ndc10 like '%517460%'


In the actual code, the 517460 is a variable, but I'm using those numbers just as an example.  So, if the DB contains data that has 517460125, this query finds it.  My problem again is running this search in reverse.  The user may enter in 0010517460125 and the query then can't find any data.  Is there anyway to take what the DB has and compare that against the searched string in a query.  Otherwise, I may have to look at writing the code in VB to do this comparison.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jonathan CMCH
Jonathan CMCH

ASKER

Thanks Wim.  I'm looking into the instr option now.
CodeCruiser and Wim, I think that's got it!  Thanks a lot.  I knew it could be done in a Query, I was just developing a migraine from trying to figure it out.

Thanks.