LIKE clause not working when used in a HAVING clause

Question

This is my query in SQL Server. I want to group by each campaign that ends with this substring '[PROS]'.

However, the resultant table I included at the end has some others ending with '[RT]'

-- Metrics for Each Date & Specific Campaign
SELECT 
    campaign_name, 
    SUM(clicks) AS Total_Clicks
FROM 
    FB_Raw
GROUP BY 
    campaign_name
HAVING 
    campaign_name LIKE '%' + '[PROS]' + '%';

04_Remarketing_BOFU_3.31.2020 [RT]                      |  568
04_Remarketing_BOFU(optimize=PUR)_5.16.2020 [RT]        |  0
02_Prospecting_CBO_BidCaps_CH_6.29.20 [PROS]            |  2741
02_Prospecting_CostCaps$500_5.12.2020 [PROS]            |  7549
04_Remarketing_Content_ATC/IC_4.10.2020 [RT]            |  478
04_Remarketing_Content_Visitors_4.30.2020 [RT]          |  381
04_Remarketing_MOFU_3.31.2020 [RT]                      |  2005
04_Remarketing_BOFU_CH_6.29.20 [RT]                     |  9
02_Prospecting_LC_Broad_Narrative [PROS]                |  6261
02_Prospecting_CBO_BidCaps_OGCreative_6.9.2020 [PROS]   |  1405
02_Prospecting_LC_Broad_6.30.2020 [PROS]                |  2512

Igor · Accepted Answer · 2022-03-09T13:27:52.787

7

You must use the ESCAPE character as the square brackets are used for Pattern matching when using LIKE. The escape character goes in front of any character you want to use the literal value of.

HAVING should be used for filtering on an aggregate result. WHERE is for filtering the data before it is aggregated (when you are using aggregates). In your case you should use WHERE instead of HAVING.

SELECT campaign_name, 
       SUM(clicks) AS Total_Clicks
FROM FB_Raw
WHERE campaign_name LIKE '%![PROS!]%' ESCAPE '!'
GROUP BY campaign_name;

edited Mar 09 '22 at 13:27

answered Mar 08 '22 at 18:58

Igor

60,821
10
100
175

Tysm sir, that was it. '[' were messing up my LIKE clause. – Danyal Danish Mar 08 '22 at 19:09
`HAVING` vs `WHERE` is not directly relevant to the question here (it's mainly a problem of escaping). Both versions would work with the correct escaping, but it's likely that `WHERE` will be more performant if predicate pushdown does not happen for the `HAVING` version. – Charlieface Mar 09 '22 at 02:25

score 1 · Answer 2 · answered Mar 08 '22 at 19:03

1

Square brackets have special meaning when used inside like clause. Without escaping, the pattern %[PROS]% matches any string containing P, R, O or S. Escape the square brackets like so:

LIKE '%[[]PROS]%'

answered Mar 08 '22 at 19:03

Salman A

262,204
82
430
521

Tysm sir, your solution also works. '[' were messing up my LIKE clause. – Danyal Danish Mar 08 '22 at 19:10

LIKE clause not working when used in a HAVING clause

2 Answers2