Escape Single Quote And Wildcard In Tsql Like

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. There are likel

Solution 1:

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]
  , casewhen a.type in ('fn', 'tf') then'Function'when a.type ='P'then'Stored Procedure'when a.type ='V'then'View'else'Unknown'endas'ObjectType', a.type
from sysobjects a
  innerjoin syscomments b on a.id = b.id
where b.[text] like'%''PD\%%'escape'\'orderby ObjectType

---

To test with two dummy procedures:

createprocedure dbo.pd_search asselect*from master..spt_values 
where number =1and name notlike'PD%'
go
createprocedure dbo.pd_search_other asselect*from master..spt_values 
where number =1and name <>'PD'
go

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

returns:

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