Use cases of Like operation (%)

 Source DE :-




Target DE :-




1. Find Gmail users: -
SELECT * FROM Source DE 
WHERE EmailAddress LIKE '%@gmail.com'

Query 


Target DE after query 


2. Users whose name starts with "A"
SELECT * FROM Source DE 
WHERE FirstName LIKE 'A%'

Query 


Target DE after query 


3.Users whose name ends with "Kapoor"
SELECT * FROM Source DE 
WHERE LastName LIKE '%Kapoor'

Query 


Target DE after query 


4. Find users with "yahoo" in email (campaign filtering)
SELECT * FROM Source DE 
WHERE EmailAddress LIKE '%yahoo%'

Query 




Target DE after query 


5. Find customers from cities containing "pur"
SELECT * FROM Source DE 
WHERE City LIKE '%pur%'

Query 


Target DE after query 



6. Target users with mobile numbers starting with 9
SELECT * FROM Source DE 
WHERE MobileNumber LIKE '9%'

Query 


Target DE after query 



7. Filter product names containing "Shaker"
SELECT * FROM Source DE 
WHERE ProductName LIKE '%Shaker%'


Query 




Target DE after query 


8. Journey re-entry check (email pattern)
SELECT * FROM Source DE 
WHERE EmailName LIKE '%Welcome%'

Query 


Target DE after query 


9. Identify test records
SELECT * FROM Source DE 
WHERE EmailAddress LIKE '%test%'


Query 


Target DE after query 


10. Domain-based segmentation (Yahoo users)
SELECT * FROM Source DE 
WHERE EmailAddress LIKE '%@yahoo.com'

Query 


Target DE after query 




🔹 - (NOT / Exclusion Logic) –Use Cases
👉 In SQL, - itself is subtraction, but in SFMC context, people mean NOT / exclusion logic (very common in segmentation)


11. Exclude Gmail users
SELECT * FROM Source DE 
WHERE EmailAddress NOT LIKE '%@gmail.com'

Query 


Target DE after query





12. Exclude unsubscribed users
SELECT * FROM Source DE 
WHERE Status != 'Unsubscribed'

Query 


Target DE after query 



13. Remove bounced emails
SELECT * FROM Source DE 
WHERE BounceCount = 0

Query 


Target DE after query 



14. Exclude test data
SELECT * FROM Source DE 
WHERE EmailAddress NOT LIKE '%test%'

Query 


Target DE after query 


15. Exclude specific domain
SELECT * FROM Source DE 
WHERE EmailAddress NOT LIKE '%@outlook.com'

Query 





Target DE after query 






































































Comments

Post a Comment

Popular posts from this blog

Use Cases of Standard Data Extensions

Use Cases of Standard Data Extensions 1. Customer Data Storage Scenario : A retail business wants to store customer information, such as names, email addresses, and purchase history, for email marketing campaigns. Solution : Use a standard data extension to store and manage customer data securely and efficiently. Data Extension Structure Field Name Data Type Length Required Primary Key SubscriberKey Text 50 Yes Yes FirstName Text 50 Yes No LastName Text 50 Yes No EmailAddress EmailAddress N/A Yes No PurchaseHistory Decimal N/A No No Email - Marketing Cloud
Read more

Data retention policy

Image
📌 Task: Create & Monitor Data Extensions with Different Retention Policies 🚀 By ElearningIT Feb 18, 2025 Your objective is to: ✅ Create three separate Data Extensions (DEs). ✅ Apply a different retention policy on each DE. ✅ Monitor & analyze the impact over time. 🔹 Step 1: Create Three Data Extensions (DEs) 1️⃣ Navigate to: Email Studio > Subscribers > Data Extensions. 2️⃣ Click "Create" → Select Standard Data Extension. 3️⃣ Define the fields for all DEs using the structure below: Field Name Data Type Length Primary Key SubscriberKey Text 50 ✅ Yes Name Text 50 ❌ No Email EmailAddress 254 ❌ No Phone Phone 50 ❌ No SignupDate Date - ❌ No 4️⃣ Create three Data Extensions with unique names: DE_IndividualRetention (For Individual Record Retention) DE_AllRecordsRetention (For All Records Retention) DE_FullDeletion (For Data Extension & Records Retention) 5️⃣ Import at least 100 records in each DE (via CSV or manual entry). 🔹 Step 2: Apply Different Retention P...
Read more