1

We have a 336 five digit zip codes that we want to target and pull through using sql in Marketing Cloud. We tried a left join where we're matching up the zip code from the table of 336 to our customer orders. The problem we're facing is we have more than five digit zip codes in our orders table.

How do I write my query to only match up the first five digits (or anything before the hyphen) from our customer orders table to the table with the targeted zip codes?

Below is what we have tried so far:

left join ZIPCODETABLE ta on ta.Zip like '%co.Zip%'

left join ZIPCODETABLE ta on ta.Zip = co.Zip
where ta.Zip LIKE '_%-'

set zipcode = left(zipcode, 5)
where right(ltrim(rtrim(zipcode)),1) = '-'
1

You can do something like this:

select
c.email
, c.firstName
, c.zip
, left(ta.zip,5) as zipMatch
, ta.zip as zipFullMatch
from CustomerDE c
left join ZIPCODETABLE ta on (left(ta.Zip,5) = left(c.zip,5))
0

I think:

left join ZIPCODETABLE ta on (left(ta.Zip,5) = left(c.zip,5))

Should be:

right join ZIPCODETABLE ta on (left(ta.Zip,5) = left(c.zip,5))

because the first table (left) is CustomerDE and the second table (right) is ZIPCODETABLE which contains the 336 five digit ZIP codes.

Reference: SQL Right Join

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.