How do I find a job that sends a specific email? I had a job sending an irrelevant email to a group of users.
The title of the email was Staging -&gt; [AUPAIR] arrivalDate trigger issue.
When I query the jobs table in the msdb database using the title above, I got no answer:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
WHERE    js.command LIKE N'%Staging -&gt; [AUPAIR] arrivalDate trigger issue%'

Only when I alter my query as below, could I find the bandit job:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
--WHERE    js.command LIKE N'Staging -&gt; [AUPAIR] arrivalDate trigger issue%'
WHERE    js.command LIKE N'%Staging -&gt; %'

This is what was the command on the jobstep:
if exists (
select count(1) as total
,year(modify_date) as [Year]
,month(modify_date) as [Month]
from repl_aupair r
where r.arrivaldate is not null
and junocore_applicationid not in(
select applicationid from junocore.dbo.replicationUpdateControl
where arrivaldatechanged = 1 )
and modify_Date &gt;='2017-01-26 00:38:46.197'
group by year(modify_date),month(modify_date)
having year(modify_date) &gt;= 2017
--order by [Year] desc
)
begin
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'DBA',
            @recipients = 'my_emails@mycompany.com',
            @body = 'APIA_Repl_Sub.dbo.repl_aupair arrivalDate not null and replicationUpdateControl arrivalDateChanged still 0',
            @subject = 'Staging -&gt; [AUPAIR] arrivalDate trigger issue ';
end

Why did my first query not work?
Conclusion:
I modified my script and the following two versions work!
After the answer by EzLo:
USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -&gt; \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'


USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -&gt; [[]AUPAIR] arrivalDate trigger issue%'
The problem with your first query is the use of unescaped brackets with the LIKE operator.

When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular expressions).

DECLARE @string VARCHAR(100) = 'ABC'

SELECT 'match!' WHERE @string LIKE '%[CX]%' -- Sting must have a C or an X
-- Result: match!


In your case, using [AUPAIR] inside the like will only match 1 letter (either A, U, P, I or R), and not the whole word.

DECLARE @string VARCHAR(100) = 'Staging -&gt; [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -&gt; [AUPAIR] arrivalDate trigger issue%'
-- Result: (none)


This will however match with the following strings (just 1 letter!):

'Staging -&gt; A arrivalDate trigger issue'
'Staging -&gt; U arrivalDate trigger issue'
'Staging -&gt; P arrivalDate trigger issue'
'Staging -&gt; I arrivalDate trigger issue'
'Staging -&gt; R arrivalDate trigger issue'


To correctly search for brackets, you can use 2 solutions:


Declare a custom ESCAPE character.

DECLARE @string VARCHAR(100) = 'Staging -&gt; [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -&gt; \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'
-- Result: match!

Enclose the opening bracket with brackets.

DECLARE @string VARCHAR(100) = 'Staging -&gt; [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -&gt; [[]AUPAIR] arrivalDate trigger issue%'
-- Result: match!



Please note that there is no need to escape the closing bracket since this one will only be interpreted as a LIKE special character as long as there is an opening one before it (which won't be if you escaped it).