Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    20

    Unanswered: finding a word, submitting part of it

    Dear All

    Does anyone know any syntax that will allow me to find entries in a atable when only part of a word is entered. I have a table that has been full txt indexed at the moment I am using the CONTAINS keyword to search fields. This works fine 'select * from tblX where contains(colX, 'gill')', gives me back what i want, but what if i type this 'select * from tblX where contains(colX, 'ill')' it doesn't find anything. What i want is a command that will search all the words in a table column. Does anyone know of a command that will do this?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: finding a word, submitting part of it

    I got into the CONTAINS (T-SQL) syntax, and I found only prefix_terms like 'ill*', but I didn't found a solution for a '*ill' condition. Using the LIKE operator isn't an option either. I hope that other guys can help you.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Well, LIKE is an option, just not a very efficient one. But, it's there for just this type of search.

    SELECT * FROM tblX WHERE colX LIKE '%ill'

    You can also replace the wildcard % with:

    _ looks for any single char

    [] looks for any chars or range of chars you specify ([a-q], [abcd], [1-9], etc)

    [^] same as above, but exclusion chars ([^a-f] means anything but a through f)
    -bpd

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Another option is to use CHARINDEX:

    where charindex('ill', colx) > 0

    I don't know how this compares to LIKE for speed, but I suspect it would be faster because it has less functionality (no wildcards) and thus may have less processing overhead.

    blindman

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I think you are right. Though, any time you ask SQL to do string processing, you are going to take a hit. They seem to have build in several levels of functionality here, getting progressively deeper as you need them. Can't think of why they would do that vs a sigle flexible function, unless the performance would dive.
    -bpd

Posting Permissions

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