在TSQL LIKE中转义单引号和通配符 [英] Escape single quote and wildcard in TSQL LIKE

问题描述

我们正在更新构建数据库中 ProjectID 字段的方式.当前，有诸如 PD80400 之类的值可标识特定项目.

We are making an update to the way we structure the ProjectID field in our database. Currently there are values such as PD80400 which identifies a specific project.

可能有存储过程在 Where 子句中使用 PDXXXXX 格式，例如 ProjectID NOT LIKE'PD％'

There are likely stored procedures which use the PDXXXXX format in the Where clause such as ProjectID NOT LIKE 'PD%'

我需要在我们的数据库中搜索任何包含对 PD％的引用的过程/视图/表/函数/等.目前，我正在使用以下脚本，但是无法捕获包含 ProjectID不喜欢'PD％'的测试过程，而没有搜索％PD％ ....返回过多的不良结果，例如带有 Update，Updated等字样的任何内容.

I need to search our database for any Procedures / Views / Tables / Function / etc. which contains a reference to PD% Currently I am using the following script but am having trouble capturing my test procedure which contains Where ProjectID NOT LIKE 'PD%' without searching for %PD%.... whcih is returning far too many undesired results such as anything with the words Update, Updated, etc.

我的脚本:

SELECT DISTINCT a.[name], b.[text], CASE WHEN a.type IN ('FN', 'TF') THEN 'Function' WHEN a.type = 'P' THEN 'Stored Procedure' WHEN a.type = 'V' THEN 'View'  ELSE 'Unknown' END AS 'ObjectType', a.type
FROM sysobjects a
INNER JOIN syscomments b on a.id = b.id
WHERE b.[text] LIKE '%PD%' AND a.name = 'AAAAAAAAAAAAA_TBG_MM_TestProcedure_PDSearch'--AND b.[text] NOT LIKE 'update%' AND b.[text] NOT LIKE 'EmpD%' AND b.[text] NOT LIKE 'updated' AND a.name NOT LIKE 'Z_OLD%' AND a.name NOT LIKE 'ZOLD%'
ORDER BY ObjectType

我应该如何格式化我的 LIKE 语句，以捕获上面列出的示例，而没有所有额外的结果?

How should I format my LIKE statement in order to capture examples like I listed above without all the extra results?

推荐答案

您可以通过指定 escape 字符来转义％通配符，并包括单引号像这样的两个单引号:

You can escape the % wildcard by specifying an escape character, and to include a single quote use two single quotes like so:

select 
    a.[name]
  , b.[text]
  , case when a.type in ('fn', 'tf') then 'Function' 
         when a.type = 'P' then 'Stored Procedure' 
         when a.type = 'V' then 'View'  
         else 'Unknown' end as 'ObjectType', a.type
from sysobjects a
  inner join syscomments b on a.id = b.id
where b.[text] like '%''PD\%%' escape '\'
order by ObjectType

---

要通过两个虚拟过程进行测试:

---

To test with two dummy procedures:

create procedure dbo.pd_search as 
select * from master..spt_values 
where number = 1
  and name not like 'PD%'
go
create procedure dbo.pd_search_other as 
select * from master..spt_values 
where number = 1
  and name <> 'PD'
go

rextester演示: http://rextester.com/KPC17170

rextester demo: http://rextester.com/KPC17170

返回:

+-----------+------------------------------------+------------------+------+
|   name    |                text                |    ObjectType    | type |
+-----------+------------------------------------+------------------+------+
| pd_search | create procedure dbo.pd_search as  | Stored Procedure | P    |
|           | select * from master..spt_values   |                  |      |
|           | where number = 1                   |                  |      |
|           |   and name not like 'PD%'          |                  |      |
+-----------+------------------------------------+------------------+------+

这篇关于在TSQL LIKE中转义单引号和通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！