% (LIKE Operator) – Use Cases

👉 % is used for partial matching / pattern search

---

👉 1. Find Gmail Users

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress LIKE '%@gmail.com'

📸 Query Execution

📸 Result (Filtered DE)

---

👉 2. Users whose name starts with "A"

SELECT *
FROM SFMC_Practice_DE
WHERE FirstName LIKE 'A%'

📸 Query Execution

📸 Result (Filtered DE)

---

👉 3. Users whose name ends with "MEHTA"

SELECT *
FROM SFMC_Practice_DE
WHERE LastName LIKE '%MEHTA'

📸 Query Execution

 

📸 Result (Filtered DE)

---

👉 4. Find users with "outlook" in email

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress LIKE '%outlook%'


📸 Query Execution



📸 Result (Filtered DE)

---

👉 5. Customers from cities containing "pur"

SELECT *
FROM SFMC_Practice_DE
WHERE City LIKE '%pur%
'

📸 Query Execution
📸 Result (Filtered DE)

---

👉 6. Mobile numbers starting with 9

SELECT *
FROM SFMC_Practice_DE
WHERE MobileNumber LIKE '9%'


📸 Query Execution

📸 Result (Filtered DE)

---

👉 7. Product names containing "Bottle"

SELECT *
FROM SFMC_Practice_DE
WHERE ProductName LIKE '%Bottle%'


📸 Query Execution



📸 Result (Filtered DE)

---

👉 8. Journey re-entry check (Welcome emails)

SELECT *
FROM SFMC_Practice_DE
WHERE EmailName LIKE '%Welcome%'


📸 Query Execution


📸 Result (Filtered DE)

---

👉 9. Identify test records

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress LIKE '%test%'


📸 Query Execution



📸 Result (Filtered DE)

---

👉 10. Yahoo Users

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress LIKE '%@yahoo.com'


📸 Query Execution
📸 Result (Filtered DE)

---

🔴 NOT / Exclusion Logic – Use Cases

👉 Used for suppression, filtering & cleaning data

---

👉 11. Exclude Gmail users

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress NOT LIKE '%@gmail.com'

📸 Query Execution


📸 Result (Filtered DE)

---

👉 12. Exclude unsubscribed users

SELECT *
FROM SFMC_Practice_DE
WHERE Status != 'Unsubscribed'

📸 Query Execution




📸 Result (Filtered DE)

---

👉 13. Remove bounced emails

SELECT *
FROM SFMC_Practice_DE
WHERE BounceCount = 0

📸 Query Execution
📸 Result (Filtered DE)

---

👉 14. Exclude test data

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress NOT LIKE '%test%'

📸 Query Execution





📸 Result (Filtered DE)

---

👉 15. Exclude internal domain

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress NOT LIKE '%@internal.com'

📸 Query Execution

📸 Result (Filtered DE)

---

👉 16. Remove duplicate emails

SELECT EmailAddress
FROM SFMC_Practice_DE
GROUP BY EmailAddress
HAVING COUNT(*) = 1
            || 
            \/
SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress IN (
    SELECT EmailAddress
    FROM SFMC_Practice_DE
    GROUP BY EmailAddress
    HAVING COUNT(*) = 1
) 
1. GROUP BY EmailAddress

    👉 Groups records based on each unique email

2. HAVING COUNT(*) = 1

    👉 Keeps only emails that appear once (no duplicates)

3. EmailAddress IN (...)

    👉 Filters main data to include only those unique emails

4. Final Query

    👉 Returns full records for non-duplicate (unique) email users
📸 Query Execution




📸 Result (Filtered DE)
All records email appears only once
 

---

👉 17. Exclude customers who already purchased

SELECT *
FROM SFMC_Practice_DE
WHERE CustomerID NOT IN (
   SELECT CustomerID FROM Orders_DE
)

 1. SELECT CustomerID FROM Orders_DE

👉 Gets all CustomerIDs who have already made a purchase


2. CustomerID NOT IN (...)


👉 Filters out those customers from the main data

3. Final Query


👉 Returns customers who have NOT purchased yet

 
Orderd DE


📸 Query Execution

📸 Result (Filtered DE)
Excludes customers who exist in Orders_DE , Returns only non-purchasing customers

---

👉 18. Suppression List Exclusion

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress NOT IN (
   SELECT EmailAddress FROM Suppression_List
)
📸 Query Execution




📸 Result (Filtered DE)

---

👉 19. Exclude inactive users (last 90 days)

SELECT *
FROM SFMC_Practice_DE
WHERE LastLoginDate > DATEADD(day, -90, GETDATE())
 
1. GETDATE():-Gives today’s date (current system date)

                 Example: 2026-04-29

2. DATEADD(day, -90, GETDATE()) :- Subtracts 90 days from today
                 
                Example: "2026-04-29" → "2026-01-29"

3. Condition :-LastLoginDate > 2026-01-29

📸 Query Execution





📸 Result (Filtered DE)

✔ Include users who logged in after Jan 29

✔ Exclude users who logged in before that

---

👉 20. Exclude specific campaign audience

SELECT *
FROM SFMC_Practice_DE
WHERE CustomerID NOT IN (
   SELECT SubscriberKey FROM Sent_Data_Extension
)
📸 Query Execution




📸 Result (Filtered DE)

---

 Combined Real-World Query (MOST IMPORTANT)

SELECT *
FROM SFMC_Practice_DE
WHERE EmailAddress LIKE '%@gmail.com'
AND EmailAddress NOT LIKE '%test%'

 1. EmailAddress LIKE '%@gmail.com'
    👉 Selects users whose email ends with @gmail.com

 2. EmailAddress NOT LIKE '%test%'
    👉 Excludes emails containing the word "test"

 3. Condition
    👉 Returns users who are Gmail users and not test users

📸 Query Execution





📸 Result (Filtered DE)

---