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,562
  • 11
  • 31
  • 50
JaylovesSQL
  • 23
  • 1
  • 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,198,228
  • 53
  • 572
  • 709
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
  • 697,062
  • 78
  • 1,055
  • 1,465
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,622
  • 1
  • 11
  • 20