4

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?

9

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 ']'        ]
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.