Sql – Full-text search across concatenated columns

full-text-searchsql serversql-server-2005

I'm new to free-text search, so pardon the newbie question. Suppose I have the following full-text index:

Create FullText Index on Contacts(
    FirstName,
    LastName,
    Organization
)
Key Index PK_Contacts_ContactID
Go

I want to do a freetext search against all three columns concatenated

FirstName + ' ' + LastName + ' ' + Organization

So that for example

Searching for jim smith returns all contacts named Jim Smith
Searching for smith ibm returns all contacts named Smith who work at IBM

This seems like it would be a fairly common scenario. I would have expected this to work:

Select c.FirstName, c.LastName, c.Organization, ft.Rank
from FreeTextTable(Contacts, *, 'smith ibm') ft
Left Join Contacts c on ft.[Key]=c.ContactID
Order by ft.Rank Desc

but this is apparently doing smith OR ibm; it returns a lot of Smiths who don't work at IBM and vice versa. Surprisingly, searching for smith AND ibm yields identical results.

This does what I want…

Select c.FirstName, c.LastName, c.Organization
from Contacts c 
where Contains(*, 'smith') and Contains(*, 'ibm')

…but then I can't parameterize queries coming from the user — I would have to break up the search string into words myself and assemble the SQL on the fly, which is ugly and unsafe.

Best Answer

The usual approach I take is to create a search view or calculated column (using a trigger) that puts all of those values into a single field.

The other thing I do is to use a full-text search engine- such as Lucene/Solr.

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Vim clear last search highlighting

To turn off highlighting until the next search:

:noh

Or turn off highlighting completely:

set nohlsearch

Or, to toggle it:

set hlsearch!

nnoremap <F3> :set hlsearch!<CR>

Related Topic