question

siugoalie78 avatar image
siugoalie78 asked

Full Text search contains clause

Hopefully this question makes sense - I am not that experienced with full-text searches. I have created a table with several columns covered by a full-text catalog. I have a query using a "Contains" clause to search for a string in those columns. Is there a way to not only return the rows found with that search, but also indicate the column in which the string was found? So, the result would show something like , row X, found in column Y of the table.
sql-server-2008-r2full-text
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Point of clarification, I am not specifying a column name in the query, but rather using an asterisk i.e. contains(*, 'blahblah')
0 Likes 0 ·
Can you please post some example code and/or data?
0 Likes 0 ·
John, unfortunately, that is easier said than done. My SQL environment is on an isolated network, and my machine that has internet access does not have SQL. But, if I don't get an answer by this evening, I will try to post something from home.
0 Likes 0 ·
siugoalie78 avatar image
siugoalie78 answered
I think what I may end up doing is creating a multivalue parameter with a list of all the available columns, then letting the user pick which ones he wants to search, and passing that dynamically into a stored procedure to do the Contains search, on just those columns. At least that will let them specify they wanted to search for the term in certain columns instead of others.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
Interesting. I don't think so directly. I'm happy to be corrected on this. But you could probably approximate it with full outer joins: select MasterId = coalesce(C1,C2,C3) , Found_C1 = case when C1 is null then 0 else 1 end , Found_C2 = case when C2 is null then 0 else 1 end , Found_C3 = case when C3 is null then 0 else 1 end from ( select C1 = ColumnId from MyTable where contains(Column1,'Search Term') ) dt1 full outer join ( select C2 = ColumnId from MyTable where contains(Column2,'Search Term') ) dt2 on dt1.C1 = dt2.C2 full outer join ( select C3 = ColumnId from MyTable where contains(Column3,'Search Term') ) dt3 on dt2.C2 = dt3.C3 I don't have any FTI to test on, so I don't know if this would work, or if the syntax is correct! Also, from a performance point of view, it's obviously increasing the number of times you search the index, so might have a bit of a negative impact. Edit: incorrect join expression in original post, plus test here (because I was curious): create table #MyTable (Id int, Column1 char(1), Column2 char(1), Column3 char(1)) insert #MyTable values (1,'A',null,null),(2,null,'B',null),(3,null,null,'C') select MasterId = coalesce(C1,C2,C3) , Found_C1 = case when C1 is null then 0 else 1 end , Found_C2 = case when C2 is null then 0 else 1 end , Found_C3 = case when C3 is null then 0 else 1 end from ( select C1 = Id from #MyTable where Column1 = 'A' ) dt1 full outer join ( select C2 = Id from #MyTable where Column2 = 'B' ) dt2 on dt1.C1 = dt2.C2 full outer join ( select C3 = Id from #MyTable where Column3 = 'C' ) dt3 on dt2.C2 = dt3.C3 /* MasterId Found_C1 Found_C2 Found_C3 ----------- ----------- ----------- ----------- 2 0 1 0 1 1 0 0 3 0 0 1 */
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply. Yeah, i may end up going that route, but it is several columns, so hopefully there is a more efficient way.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.