1

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?

Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64

2 Answers2

3

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.

Community
  • 1
  • 1
Keith
  • 20,636
  • 11
  • 84
  • 125
  • You're right. SQL Server has no equivalent of pg_trgm - trigram search module from PostgreSQL. It's possible to write own trigram serach mechanism, but it uses complex queries and procedures: https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search. Pg_trgm is already optimised and easy to use. – Marek Marczak Feb 19 '21 at 22:17
0

Add Full-Text index if you want.

You can search the table using CONTAINS:

SELECT *
FROM YourTable
WHERE CONTAINS(TableColumnName, 'SearchItem')
Suvro
  • 352
  • 2
  • 13