CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Ahmet Altun
Ahmet Altun

Reputation: 4049

SQL Server Efficient Search for LIKE '%str%'

In Sql Server, I have a table containing 46 million rows. In "Title" column of table, I want make search. The word may be at any index of field value.

For example:

Value in table: BROTHERS COMPANY

Search string: ROTHER

I want this search to match the given record. This is exactly what LIKE '%ROTHER%' do. However, LIKE '%%' usage should not be used on large tables because of performance issues. How can I achieve it?

Upvotes: 1

Views: 1799

Answers (2)

Keith
Keith

Reputation: 21264

Though I don't know your requirements, your best approach may be to challenge them. Middle-of-the-string searches are usually not very practical. If you can get your users to perform prefix searches (broth%) then you can easily use Full Text's wildcard search (CONTAINS(*, '"broth*"')). Full Text can also handle suffix searches (%rothers) with a little extra work.

But when it comes to middle-of-the-string searches with SQL Server, you're stuck using LIKE. However you may be able to improve performance of LIKE by using a binary collation as explained in this article. (I hate to post a link without including its content but it is way too long of an article to post here and I don't understand the approach enough to sum it up.)

If that doesn't help and if middle-of-the-string searches are that important of a requirement then you should consider using a different search solution like Lucene.

Upvotes: 3

Md Rezowan Hossain Suvro
Md Rezowan Hossain Suvro

Reputation: 380

Add Full-Text index if you want.

You can search the table using CONTAINS:

SELECT *
FROM YourTable
WHERE CONTAINS(TableColumnName, 'SearchItem')

Upvotes: 0

Related Questions