主页 / dba / 问题 / 187364
Accepted
SqlWorldWide
Asked: 2017-10-01 19:09:37 +0800 CST

需要 LIKE 运算符和方括号方面的帮助

  • 772

表列中的样本数据，数据类型NVARCHAR(MAX)。此数据由扩展事件跟踪捕获实体框架生成的数据库调用收集。

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

我想使用LIKE运算符来查找所有带有 text 的行FROM [dbo].[customer]。

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

为什么它不起作用？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    无需转义右括号。如果没有左括号，SQL Server 不会将右括号视为占位符的一部分。

    因此，您应该只转义左括号，使用括号或定义转义字符（我个人认为在这种情况下更具可读性）。

    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在这里

    该文档指定了两种转义方法并具体说明（在标题下使用通配符作为文字）：

    Symbol          Meaning
LIKE '[[]'      [
LIKE ']'        ]
    • 9

相关问题