Overcome LIKE character length limitation

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.

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.

StackExchange Database Administrators Q#175068, answer score: 9