Need help with LIKE operator and square brackets

Question

Sample data in a table column, data type NVARCHAR(MAX). This data is collected by extended event trace capturing database calls generated by Entity Framework.

exec sp_executesql N'SELECT TOP (2) 
    [Extent1].[ccusqcode] AS [ccusqcode], 
    [Extent1].[ccusname] AS [ccusname], 
    [Extent1].[ccusfirstname] AS [ccusfirstname], 
    [Extent1].[ccuslastname] AS [ccuslastname], 
    [Extent1].[dbirthday] AS [dbirthday], 
    [Extent1].[ccellphone] AS [ccellphone], 
    [Extent1].[cdriverlicense] AS [cdriverlicense], 
    [Extent1].[csocsecno] AS [csocsecno], 
    [Extent1].[dfirstvisit] AS [dfirstvisit], 
    [Extent1].[dlastvisit] AS [dlastvisit], 
    [Extent1].[nmtdrxcount] AS [nmtdrxcount], 
    [Extent1].[lduplabel] AS [lduplabel], 
    [Extent1].[cemployerid] AS [cemployerid], 
    [Extent1].[mnotes] AS [mnotes], 
    [Extent1].[cemployercontact] AS [cemployercontact], 
    FROM [dbo].[customer] AS [Extent1]
    WHERE ([Extent1].[cusid_PK] = @p__linq__0) AND ([Extent1].[pharminfoid_FK] = @p__linq__1)',N'@p__linq__0 bigint,@p__linq__1 bigint',@p__linq__0=NULL,@p__linq__1=1

I want to use LIKE operator to find all rows with text FROM [dbo].[customer].

SELECT * 
  FROM [dbname].[schemaName].[tableName]
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%'

Why it does not work?

Answer 1

There is no need to escape the closing bracket. If there is no opening bracket SQL Server will not consider the closing bracket as part of a placeholder.

So you should be fine escaping only the opening bracket, either using brackets or by defining an escape character (which I personally find more readable in this case).

CREATE TABLE statements (statement nvarchar(max));

INSERT INTO statements (statement) 
VALUES (N'SELECT TOP (1)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[product] AS [Extent1] WHERE whatever')

-- won't return anything
SELECT * 
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%';

-- should return 2 records  
SELECT * 
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo].[[]customer]%';

-- should return 2 records  
SELECT * 
  FROM statements
  WHERE STATEMENT like '%FROM \[dbo].\[customer]%' ESCAPE '\';

DBFiddle here

The documentation specifies both methods of escaping and specifically states (under the heading Using Wildcard Characters As Literals):

Symbol          Meaning
LIKE '[[]'      [
LIKE ']'        ]