LIKE clause not working when used in a HAVING clause-CodePudding

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

CodePudding user response：

HAVING is 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 need to 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;

Note that you must also use ESCAPE as the square brackets have a special meaning when using LIKE.

CodePudding user response：

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]%'