Search by keyword or words?

Results 1 to 3 of 3

Thread: Search by keyword or words?

  1. #1
    Join Date
    Dec 1969

    Default Search by keyword or words?

    I am trying to create a query that searches for key words in text fields for a web based helpdesk application. Currently I am using the like operator but if I do a search on problems with ACT (a contact management app) if will find all cases with those letters in the history. Example below:<BR><BR>Select * from cases where case_history like &#039;%ACT%&#039;<BR><BR>Finds the word ACT but also contact, actor, contractor, and so on. I do need it to find words like updates if the user types update or printing if the user types printer. Also the above query only finds words that are grouped together, so if the user types ACT printing problem, if will only find cases that have that exact phrase instead of just the three keywords.<BR><BR><BR>Any help would be greatly appriciated<BR>Dan

  2. #2
    Join Date
    Dec 1969

    Default RE: Search by keyword or words?

    Syntax depends on database used.<BR>But if doing a serch add a space before and After the word.<BR><BR>WHERE (((YourField) Like "% Act %" Or (YourField) Like "% Printer %" Or (YourField) Like "% Problem %"))<BR><BR>Only thing is it will not find the word if its at end of sentence<BR><BR>This solves the problem with printing. &#060;&#060;wont find printing.<BR><BR>If you are using Sql server<BR>check out full index services

  3. #3
    Join Date
    Dec 1969

    Default You'd have to split the search phrase...

    ...into separate words and then do a LIKE on each of them.<BR><BR>For example:<BR><BR>User enters: act printing problem<BR><BR>Split this string at each space (e.g. split(SearchString, " ") in VBScript or SearchString.split(" ") in JScript) and then use the resulting array to create an OR&#039;ed LIKE statement, like this:<BR><BR>SELECT * FROM cases WHERE case_history LIKE &#039;%act%&#039; OR case_history LIKE &#039;%printing%&#039; OR case_history LIKE &#039;%problem%&#039;<BR><BR>This will find you all records where case_history contains "act" or "printing" or "problem", or two out of three or all three.<BR><BR>If you want it to find only the records where ALL THREE words appear, replace the OR&#039;s with AND&#039;s.<BR><BR>If you want to go a step further, you might want to filter out characters that aren&#039;t letters or digits, because a user might type in "act, printing, problem", in which case splitting at each space will result in the search words ending on a comma, which is not what you want.<BR><BR>Then you go another step further and allow the search to find "printing" when the user enters "printer". This is a bit more complicated. You can use something called "stemming". Have a look at the following site:<BR><BR>I hope this helps,<BR>Oliver.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts