Results 1 to 7 of 7

Thread: where clause

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: where clause

    I have a field named county in the database. The county likes "Kent county". They have "County" for the value. So i need to retrieve the records. I have the following query:

    select LName, FName, Address, City, State, Zip, [COUNTY LONGNAME] as CountyLongName, from test where LName like @LName +'%' and FName like @FName +'%' and +[COUNTY LONGNAME] + like @County +'%' order by LName, FName

    It didn't retrive the record. Can i use

    like @County +'%' as @County is two strings combination.

    Thanks for your help.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    @County + '%' results in 'County%', while the value you're trying search is 'Kent county'. You need to change it to '%' + @County, but of course this is a 'NO-NO!!!' in my book
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure, 'cause it bypasses the index. But if searching within the string is a business requirement, what other options does he have?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, here's one: you can parse the current field by space, and store each word as a separate record into Keywords table. Then you can use "where exists (select...from Keywords where test.keyField = Keywords.keyField and kWord = @param)"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I've done that for a knowledge engine I created. It requires, though, more ambitious database coding and design, and so may be beyond a new DBA's capacity.
    Plus, it still only gives the ability to search for whole words. So if the business requirement is to allow searches for partial strings then I think we are still stuck with LIKE ('%' + [SearchString] + '%').
    I think the most important thing is to be smart about how the searches are implemented. I caught my developers implementing a filtered dropdown list by issuing a new SELECT search for each character that a user entered into the box. Good Lord....
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    blindman, how do you feel about full text search? Still too ambitious for a new DBA?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think the learning curve on full-text is too steep. But it may be overkill for a single search on a small application. There is administrative overhead involved, and it too will only search on full words (correct me if I am wrong) and so the simpler solutions presented above may be preferable in most circumstances.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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