Like operator ( [ ] ) in SQL

1. Matching Specific Characters in Product IDs

Query - Select * From [Product_data_abu] Where ProductCode Like '[1234]%'

How it works - 

  • [1234]% → Matches values where the first character is 1, 2, 3, or 4.

  • It excludes 5D67 and 7F90 (since their first digit isn’t in [1234]).

Source DE :-


Query 



Target DE after query :-


2.Matching Specific Numbers in Comma-Separated Lists



Query - Select * From [EcommerceUpsellDE_Abu] Where RecommendedProducts Like '[1-4]' OR RecommendedProducts Like '[1-4],%' 
OR RecommendedProducts Like '%,[1-4],%' OR RecommendedProducts Like '%,[1-4]'


How it works - 

  • Different positions in the list are checked:

    • [1-4] → Matches if the first value is 1, 2, 3, or 4.

    • [1-4],% → Matches if the first value is followed by more values.

    • %,[1-4],% → Matches if 1, 2, 3, or 4 appears anywhere in the middle.

    • %,[1-4] → Matches if 1, 2, 3, or 4 is the last value.

💡 Why is this important?
Without checking different positions, SFMC SQL wouldn’t find values inside the comma-separated list.




Source DE 


Query 




Target DE after query 


3. Using [] with Letters

Query - Select * From [Customers_Abu] Where CustomerName Like '[ABC}%'

How it works - 

  • [ABC]% → Matches names starting with A, B, or C.

  • It excludes David and Emily since their first letters aren’t in [ABC].



Source DE


Query







Target DE after query



4. Using [] to Escape Special Characters


Query -
Select * From [Product_Abu] Where Discount Like '%[%]'

How it works

  • [%] tells SQL to treat % as a normal character, instead of a wildcard.

  • Without [ ], SQL would treat % as "match any characters", leading to incorrect results.


Source DE


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