1

I have a table with values such as "F-10" or "Jim-beam". Is there a way for me to get these results if a user had searched say "F10" or "Jimbeam"? Basically, the user may not know there is a dash in the entries but I want the search to be forgiving enough to find it.

Right now I'm trying to use:

SELECT *
WHERE
    CONTAINS(table.*, ,'"F10*" Or "Jimbeam*"')
Tom H
  • 46,766
  • 14
  • 87
  • 128
Goku
  • 1,565
  • 1
  • 29
  • 62
  • What happens when you run your code? – Tom H Feb 18 '16 at 16:00
  • @TomH neither result returns. if i searched "F*" or "Jim*" then it would work, but pragmatically i cant just arbitrarily break up strings like that. – Goku Feb 18 '16 at 16:09
  • 1
    A Thesaurus file might be able to do what you need, but I don't have enough experience with them to post a complete solution on the subject. – Tom H Feb 18 '16 at 16:29

3 Answers3

0

you can create array to get the inserted value from the user

then use the like %value1% or like %value2% for each value in the array

it could be a solution

Peter
  • 162
  • 3
  • 11
  • It will search for every part of value which typed by the user , so if the user typer f10 , it will bring F-10 – Peter Feb 18 '16 at 15:56
  • how do you know where to divide the string up into arrays? where is the separator? if a user types jimbeam what makes you make value1= jim and value2=beam rather than value1=jimbe value2=am ? – Goku Feb 18 '16 at 16:08
  • you can count or use a function to divide between the numbers and characters – Peter Feb 18 '16 at 16:41
  • there are no numbers between "jimbeam" – Goku Feb 18 '16 at 17:13
0

You could try to replace the values in the database with values that do not contain any special characters. I used the function described in this answer before: https://stackoverflow.com/a/1008566/894974 So with that function installed, your where-clause would become:

where dbo.RemoveNonAlphaCharacters([columnToSearch]) like '%'+@searchString+'%'
Community
  • 1
  • 1
Sander
  • 390
  • 1
  • 4
  • 13
  • I like this answer, but it doesn't look like that function takes in a column, just a string. is there a way for me to pass in the entire column? Hoping this wont slow the search down tremendously but slow is better than nothing. – Goku Feb 18 '16 at 18:26
0

You can create a separate column where dashes are removed from these words, then perform your full text searches against that column.

For example, your table could look like this (or the new column could be part of a new table):

Id  Text            TextForFullTextSearch
-----------------------------------------
1   Jim-beam        Jimbeam
2   F-10            F10
3   blah blah       blah blah

If you need to support searches on both "Jimbeam*" and "Jim-beam*" then you could perform the full text search against both the old and new columns.

This does require you to store the text twice so there will be more gears in your process. The benefits will be in the search accuracy and performance (LIKE will be much slower), so you'll have to weigh those benefits against the increased complexity.

Some ideas for populating the new column as data is inserted and updated:

  • Handle this in your data layer, i.e. all insert and update statements should include both Text and TextForFullTextSearch.
  • Add an insert/update trigger to the table that, whenever Text is inserted or updated, simultaneously updates TextForFullTextSearch.
  • Create an automated job that continually polls the table for inserts/updates, then updates TextForFullTextSearch accordingly.
Keith
  • 20,636
  • 11
  • 84
  • 125