Search anypart of field in SQL search result

I run, and I'm having trouble modifying our search feature, it's showing results, but only if the input is exactly as written and the first part of the field.
EG if you search 6900 it gives NO RESULTS, if you search the model's exact name TM-6900 it shows the results. I'd like it so that 6900 does show the TM-6900 as this is part of the product name.
Would anyone kindly be able to advise how I need to modify this below to action this?
I'll be so very grateful! Simon, Southampton, UK
if txtType = 1  then  'manufacturer 
	txtSQL =  "SELECT *  FROM products where manufacturer LIKE '" & txtSearchText  & "%' OR Name LIKE '" & txtSearchText  & "%'"

Open in new window

Who is Participating?
97WideGlideConnect With a Mentor Commented:
Oh, and for comparison, the top line is yours and the bottom line has what I think you should consider changing it to :

The % stands in for zero or more unspecified characters.

"SELECT *  FROM products where manufacturer LIKE '"    & txtSearchText  & "%' OR Name LIKE '"    & txtSearchText  & "%'"
"SELECT *  FROM products where manufacturer LIKE '%" & txtSearchText  & "%' OR Name LIKE '%" & txtSearchText  & "%'"
txtSQL =  "SELECT *  FROM products where manufacturer LIKE '%" & txtSearchText  & "%' OR Name LIKE '%" & txtSearchText  & "%'"

Just added a few '%'.

SimonDockerAuthor Commented:
Can you show me in the code where to add the % please? And how will this resolve it?
Many thanks,
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I'll try.. someone may do a better job.

Select * from products where manufacturer LIKE '6900%';
would give all products where the manufacturer starts with 6900 and then has zero or more characters following it.  
So I think you need another % sign just before 6900 so that products would be shown if 6900 is anywhere in the manufacturer.

Please see the following link for more explanation :

Specifically, the part which talks about wildcard characters.
SimonDockerAuthor Commented:
Seems to be an improvement, the link doesn't work though. But getting more results.
So when I search 0.1dB LNB I get no results, it should show results from these fields like "0.1dB Single LNB (Diamond edition)"
Also if I search for the  TM-6900 as TM 6900, it still doesn't show results.
Any ideas?
I'm very grateful :)
Hi Simon,

The % stands in for zero or more unspecified characters at that position.

So, if you search for "0.1dB LNB" the code you wrote converts that to '%0.1dB LNB%' which does not match "0.1dB Single LNB", Right ?
Likewise, "TM 6900" does not match "TM-6900";

The type of searching you are mentioning here is somewhat different and not really what you talked about in your original post.

Could you confirm that we've solved the original problem, namely that a search for 6900 returns your expected results ?  

After that, you'll need to tokenize your entered search string and pass it to the SQL engine to get the results you mention here.

ALSO, I just noticed that you are searching on both manufacturer and name.  Keep in mind that by adding the % sign you are searching on substrings existing in both columns.

SimonDockerAuthor Commented:
OK I see your point.
I'll certainly archive that as a solution.
How can I get around the LNB example you showed me, any chance or dropping it into the code again?
Thanks as always :)
P.S. This is my first time on this website, excuse me not knowing etticate.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.