SQL Server – Using LIKE Operator with Square Brackets

sql serversql-server-2012t-sql

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?

Best Answer

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 ']'        ]
Related Question