CODEWITHSUNDEEP
sqlsql-server
NewUnhandledException
NewUnhandledException

Reputation: 743

Full text search on multiple columns sql server

I have the following table 

Id  Author
1   Alexander Mccall Smith
2   Ernest Hemingway
3   Giacomo Leopardi
4   Henry David Thoreau
5   Mary Higgins Clark
6   Rabindranath Tagore
7   Thomas Pynchon
8   Zora Neale Hurston
9   William S. Burroughs
10  Virginia Woolf
11  William tell

I want to search the Author by putting first few characters of the first and last name.

eg: Search Text: Will tel
Then the search result show the following result

William tell

eg: Search Text: will Burrou
Then the search result show the following result

William S. Burroughs

eg: Search Text: Will Then the search result show the following result

William S. Burroughs William tell

What is the efficient way to achieve this in sql server ?

Upvotes: 1

Views: 1215

Answers (2)

Adam
Adam

Reputation: 2440

Less efficient than @Igor's answer as the table size grows, but you can also use the Like statement.

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

-- example 1 searching on Will and Tel
SELECT Id, Author
FROM Authors
WHERE Author Like('Will%Tel%')

-- example 2 searching on Will and Burrou
SELECT Id, Author
FROM Authors
WHERE Author Like('Will%Burrou%')

-- example 3 searching on Will
SELECT Id, Author
FROM Authors
WHERE Author Like('Will%')

Cons: It is slower than the contains statement.You need to include the % sign after any other keyword you're looking to search for.

Pros: Can be faster than contains statement in cases of smaller(<1000) row tables.

Upvotes: 0

Igor
Igor

Reputation: 62213

As you mentioned this can be achieved using Full Text Search. You have to create the FTS catalog and then index on the table and column(s). You stated in the title 'Columns' but I only see one table column in your example so I will create the queries using that.

-- example 1 searching on Will and Tel
SELECT Id, Author
FROM Authors
WHERE CONTAINS(Author, '"Will*" AND "tel*"')

-- example 2 searching on Will and Burrou
SELECT Id, Author
FROM Authors
WHERE CONTAINS(Author, '"will*" AND "Burrou*"')

-- example 3 searching on Will
SELECT Id, Author
FROM Authors
WHERE CONTAINS(Author, '"will*"')

For further reference see

Upvotes: 5

Related Questions