在TSQL中使用单引号和通配符LIKE
问题描述:
我们正在对数据库中构建ProjectID
字段的方式进行更新。目前存在诸如PD80400
的值,其识别特定项目。在TSQL中使用单引号和通配符LIKE
有可能保存其使用PDXXXXX
格式Where
条款的方法,如ProjectID NOT LIKE 'PD%'
我需要寻找我们的数据库中的任何程序/浏览/表/功能/等,它包含一个参考
PD%
目前我正在使用以下脚本,但在捕获包含Where ProjectID NOT LIKE 'PD%'
而没有搜索%PD%
....的测试过程时遇到了问题......它返回的结果太多太多,例如任何与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
声明,以捕捉例子像我上面列出所有没有额外的结果吗?
答
您可以通过指定escape
字符躲避%
通配符,并包括一个单引号使用两个单引号,像这样:
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
要使用两个虚拟程序进行测试:
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
个回报:
+-----------+------------------------------------+------------------+------+
| 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%' | | |
+-----------+------------------------------------+------------------+------+
好极了!非常感谢,这比我希望的效果更好 – gruff
@gruff高兴地帮忙! – SqlZim