0

I want to pull in all contacts whose postal code begins with L5h, K2S or L3S.

My sql is:

SELECT * 
FROM [customer_list_DE]
WHERE Postal_Code IN ('L5H%','K2S%','L3S%')

I have checked my data and many records exist with postal code that start with those characters, but my query is resulting in 0 records (however it is not erroring out). I am using Salesforce Marketing Cloud.

Tom Aranda
  • 5,919
  • 11
  • 35
  • 51
JaylovesSQL
  • 33
  • 2
  • 5
  • Possible duplicate of [SQL Server using wildcard within IN](https://stackoverflow.com/questions/1076097/sql-server-using-wildcard-within-in) – Troy Turley Mar 21 '18 at 13:36

3 Answers3

2

You need OR. IN doesn't do wildcards:

SELECT * 
FROM [customer_list_DE]
WHERE Postal_Code = 'L5H%' OR Postal_Code = 'K2S%' OR Postal_Code = 'L3S%';

You could also do this with string manipulation:

SELECT * 
FROM [customer_list_DE]
WHERE LEFT(Postal_Code, 3) IN ('L5H', 'K2S', 'L3S')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

IN list does not support wildcards. Use OR instead:

SELECT * 
FROM [customer_list_DE]
WHERE Postal_Code LIKE 'L5H%'
   OR Postal_Code LIKE 'K2S%'
   OR Postal_Code LIKE 'L3S%'
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
0

Try this instead:

SELECT *
FROM [customer_list_DE]
WHERE Postal_Code LIKE 'L5H%'
   OR Postal_Code LIKE 'K2S%'
   OR Postal_Code LIKE 'L3S%';
Robert Kock
  • 5,795
  • 1
  • 12
  • 20