Results 1 to 6 of 6

Thread: Fuzzy Search

  1. #1
    Join Date
    Nov 2010
    Posts
    10

    Unanswered: Fuzzy Search

    Does anyone know of a good add on that will allow fuzzy text searches?
    (sql server 2008)

    Specifically, I'm building up a database of how much various items, eg second hand cameras sell for on eBay. I've already written the code to download the data. But to query the database is tricky, because people keep misspelling the names of the cameras. For example, a Canon IXUS 90 IS might be entered as Canon 9015. Or they might write Cannon instead of Canon. I want a search that is intelligent enough to recognise these as near misses.

    Any advice on a suitable add on?

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I don't know of any addon for sql, but soundex and difference functions might do this for you with some programming logic to handle the results.

    -- Soundex
    select soundex('Canon IXUS 90'),soundex('Gannon 15')

    -- Difference - Change the when comparison strings to test this code snippet
    select case when difference('Canon IXUS 90','Cannon 9015') = 0 then 'no matching chars'
    when difference('Canon IXUS 90','kannon 9015') = 1 then 'do something <-- 1 matching chars'
    when difference('Canon IXUS 90','Cannon 9015') = 2 then 'do something <-- 2 matching chars'
    when difference('Canon IXUS 90','Cannon 9015') = 3 then 'do something <-- 3 matching chars'
    when difference('Canon IXUS 90','Caknon 9015') = 4 then 'do something <-- best match'
    else 'do something else' end

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Won't Full-Text Search (SQL Server) work?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Pat Phelan View Post
    Possibly if the data was text and not a varchar column in a table. Although the op said text, I wonder if op meant column data? Don't know the db table design.

    "such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word."

    Sounds like they are using soundex or different functions. A bit more complex though.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    I have a couple of those laying around somewhere. I also have one that will do a search on every column, table in the db for a specific string. I use it to look for table(s), row, column where you might find 'test'.

Posting Permissions

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