CODEWITHSUNDEEP
sqlsql-server
JaylovesSQL
JaylovesSQL

Reputation: 43

Pulling in all contacts with specific postal codes using sql

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.

Upvotes: 0

Views: 379

Answers (3)

Robert Kock
Robert Kock

Reputation: 6028

Try this instead:

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

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

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%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270371

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')

Upvotes: 2

Related Questions