Search anypart of field in SQL search result

Posted on 2008-11-12
Last Modified: 2013-09-14
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

Question by:SimonDocker
    LVL 8

    Expert Comment

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

    Just added a few '%'.


    Author Comment

    Can you show me in the code where to add the % please? And how will this resolve it?
    Many thanks,
    LVL 8

    Expert Comment

    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.
    LVL 8

    Accepted Solution

    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  & "%'"

    Author Comment

    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 :)
    LVL 8

    Expert Comment

    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.


    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
    It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now