0

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).

Alexander Abakumov
  • 13,617
  • 16
  • 88
  • 129
  • i just dealt with the 8k search limitation problem, but it was on text datatype, not on varchar(max). when i switched from text to varchar(max) the charindex worked just fine. p.s. i'm using mssql 2005 – shayuna Jul 02 '18 at 13:50

2 Answers2

3

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

Hamburglar
  • 550
  • 3
  • 17
  • That's an interesting approach, but what if the first part of the search string entry goes to the one chunk and the rest of it to the following one? Sems like we need some additional logic to handle this cases. – Alexander Abakumov Feb 02 '17 at 22:34
  • Yea i think that this would actually make the max search term size 4k not 8k – Hamburglar Feb 02 '17 at 22:36
0

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

Markus
  • 2,184
  • 2
  • 22
  • 32
  • I did much the same thing, but my tests failed. Checking Books Online, the entries for both LIKE and CHARINDEX state that they only work on the first 8000 characters. I was using SQL 2008. What version were you using? – Philip Kelley Feb 02 '17 at 23:04
  • SQL Server 2016 SP1 – Markus Feb 02 '17 at 23:07
  • Might have changed. Can you check BOL, see if the two no longer have limits? – Philip Kelley Feb 02 '17 at 23:10
  • I tested it on SQL 2008 R2 and SQL 2016 and it works. The Doku on MSDN for the like operator also does not say anything about this limit. The only limit you have is on the search pattern. There you can use only a maximum of 8K. But the searched value can be as big as varchar(max) can hold. (I could not try varbinary(max) because I don't know how to do a like search on varbinary... How is this possible?) – Markus Feb 03 '17 at 10:03
  • Could you please check how your test query differs from [this post](https://www.bennadel.com/blog/966-sql-charindex-has-data-size-limitations.htm)? – Alexander Abakumov Feb 03 '17 at 15:14
  • This post is from 2007... Are you using a Version of SQL Server bevor 2008? If yes, its time to update! - My Query: SELECT Charindex('aa', longText) FROM [TextContext].[dbo].[VarCharRecords] - The Result: 20001 Here the Charindex is definitely after 8K. – Markus Feb 03 '17 at 21:00