Overcome LIKE character length limitation

Question

By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause.

I'm trying to fetch the query plan from query plan cache for a particular query.

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where st.text like '%MY_QUERY_LONGER_THAN_4000_CHARS%' ESCAPE '?'

if the query inside the the LIKE is longer than 4000 chars then I get 0 results even if my query is in the cache plan. (I was expecting at least an erorr).

Is there a way to workaround this issue or to do it differently? I have queries which can be > 10000 chars long and it look like I can't find them with the LIKE.

Solomon Rutzky · Answer 1 · 2020-02-21T00:02:45.057

It does not appear that this can be solved in pure T-SQL since neither CHARINDEX nor PATINDEX allow for using more than 8000 bytes in the "to search for" string (i.e. max of 8000 VARCHAR or 4000 NVARCHAR characters). This can be see in the following tests:

SELECT 1 WHERE CHARINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                         N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

SELECT 1 WHERE PATINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                        N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

Both of those queries return the following error:

Msg 8152, Level 16, State 10, Line xxxxx
String or binary data would be truncated.

And, reducing the 7000 in either of those queries down to 3999 gets rid of the error. A value of 4000 in both cases will also error (due to the extra N'Z' character at the beginning).

HOWEVER, this can be accomplished using SQLCLR. It is fairly simple to create a scalar function that accepts two input parameters of type NVARCHAR(MAX).

The following example illustrates this ability using the Free version of the SQL# SQLCLR library (which I created, but String_Contains is again available in the Free version :-).

SETUP

-- DROP TABLE #ContainsData;
CREATE TABLE #ContainsData
(
  ContainsDataID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Col1 NVARCHAR(MAX) NOT NULL
);

INSERT INTO #ContainsData ([Col1])
VALUES (N'Q' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15000)),
       (N'W' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 20000)),
       (N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 70000));

-- verify the lengths being over 8000
SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp;

TESTS

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15100)) = 1;
-- IDs returned: 2 and 3

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 26100)) = 1;
-- IDs returned: 3

Please keep in mind that String_Contains is using an everything-sensitive (case, accent, Kana, and width) comparison.

score 2 · Answer 2 · answered Jun 01 '17 at 15:32

Because you also asked for alternative approaches, another way to find a specific plan is to search for its plan_hash, by altering your query as follows:

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
INNER JOIN sys.dm_exec_query_stats qs
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE qs.query_hash = 0xE4026347B5F49802

The quickest way I've found to get the QueryHash value to search for is to paste the query in question into a Query Window and then Display the Estimated Execution Plan. Read the XML output and look for the QueryHash attribute in the StmtSimple element and this should give you what you need. Plug the QueryHash value into the query above and hopefully you should have what you're looking for.

Here's some screenshots showing how to quickly get the QueryHash value in case I'm explaining it poorly.

Display Estimated Execution Plan

Show Execution Plan XM...

Search for QueryHash Value

Obviously the trick won't work if the query you're looking for differs from the query you're displaying the Estimated Execution Plan for, but this may be quicker than all the nuance that comes with CLR routines and getting those to work properly.

score 0 · Answer 3 · answered Jun 01 '17 at 20:21

If you have access to the query texts (meaning you can modify them), you could add unique comments to those you are interested in:

select /* myUniqueQuery123 */ whatever from somewhere ...

then search for myUniqueQuery123 in the plan cache instead of the entire query text:

... where st.text like '%myUniqueQuery123%'

PS. Not tested

Overcome LIKE character length limitation

3 Answers3