Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WabbaStang
Starting Member
4 Posts |
 Posted - 2009-05-11 : 22:49:51
|
| Trying to find a way to search for a set of keywords in a combined set of varchar fields. Essentially I want to combine multiple fields and search it as if it's a single string, rather than all the individual ones. That way the search words could be in one field or another, or divided between them, and still be returned. Basically what I WANT to do is: CONTAINS((t1.thisfield,t2.thatfield), '"Hello" AND "world"') ... orWHERE t.thisfield + t2.thatfield LIKE '%Hello%' AND t.thisfield + t2.thatfield LIKE '%world%'.. Except that neither of these actually WORK. Obviously CONTAINS won't search across tables. The last example functions, but for reasons that I don't understand, it will not give me all of the results that it should according to how I logically expect it to work. It seems to only return the results that have all of the search words contained in one of the fields on the list, rather than any/all of the fields. Is there some syntax i am missing that will make this function correctly? It's a dynamically generated query that I feed a list of search fields and a search string to; the search string gets broken down into individual words and then selects all of the words in the list across the combined fields. I am no SQL expert and do not have time to be, just tweaking on an existing system. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-12 : 10:05:22
|
| the second statement looks fine as long as both the concatenated fields are of not null type. If any of them can contain null values then it wont work. try this tooWHERE COALESCE(t.thisfield,'') + COALESCE(t2.thatfield,'') LIKE '%Hello%' AND COALESCE(t.thisfield,'') + COALESCE(t2.thatfield,'') LIKE '%world%' |
 |
|
|
WabbaStang
Starting Member
4 Posts |
Posted - 2009-05-12 : 12:30:34
|
| One of the string columns did allow NULLs, and I didn't realize that would cause it to not work. Changed the table and all is well. Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-13 : 10:24:17
|
welcome  |
|
|
|
|
|
|
|
|
Search in combined columns across tables