CODEWITHSUNDEEP
sql-servert-sqlvarchartext-searchvarcharmax
Alexander Abakumov
Alexander Abakumov

Reputation: 14579

Search within all text in varchar(max) column

As a result of Googling, it turns out that both LIKE operator and CHARINDEX() function search only first 8K of varchar(max) column.

  1. Is this a correct statement?
  2. If so, how to search for a substring in full text of varchar(max) column without enabling full text search feature (if this even makes sense)?

UPDATE: Search beyond 8K doesn't work for me on SQL Server 2008 (10.0.5538.0).

Upvotes: 0

Views: 3052

Answers (2)

Markus
Markus

Reputation: 2281

I have never heard of this limitation! I just testet it to be sure and I inserted 10K and 20K of data in a varchar(max) col and searched for the last part not occuring in the first 8K with like and SQL Server found the row.

So the answer to the first question is: NO

Upvotes: 0

Hamburglar
Hamburglar

Reputation: 550

One way to do this would be to create a view on this table that partitions the column into 8k chunks with SUBSTRING. In addition to these 0 indexed partitions partition the column in 8k chunks starting at the 4kth char.

You can then apply a LIKE clause to each of these columns and if any column matches the row is a match.

There are obvious limitations to this approach (search term must be under 8k, search param must start and end with wildcards) but enabling full text search is the real answer

Upvotes: 3

Related Questions