1

I've got 2 table I want to join:
- a zip code / region table correspondance for one country with the beginning of zipcode
- a contact table with email address (subscriberkey), a full zip code and a region column which I want to populate

How do I populate the region zip code in my contact table by using the beginning of zip codes in my correspondance table?

I thought of

SELECT C.email and C.full_zipcode
FROM CONTACTS C
JOIN T.begin_zipcode% on C.full_zipcode

CASE WHEN C.full_zipcode LIKE CONCAT(T.begin_zipcode, '%') THEN T.REGION_WANTED

END as C. REGION

Of course it won't work

Is that possible to do what I want to do?

Thanks!

1 Answer 1

2

You will be able to do this by joining on the Left number of characters of the full zipcode i.e. Left(full_zipcode,4) will give you the first 4 characters

You won't be able to select from Contacts data extension and update the output to Contacts Table - you will have to save your result in another data extension. If you do then wish to update the Contact table, then a second SQL should copy from the output data extension and update your Contacts Data Extension.

SELECT 
C.email 
,C.full_zipcode

FROM CONTACTS C with (NOLOCK)

LEFT JOIN REGIONTABLE t with (NOLOCK) on 
 t.begin_zipcode = LEFT(C.full_zipcode, 4)  
2
  • Humm What if my begin of zipcode is not always the same length ? usually 4 but sometimes 2?
    – Emilie
    Feb 21, 2018 at 17:12
  • In that case - try your join on : LEFT JOIN REGIONTABLE t with (NOLOCK) on t.begin_zipcode like C.full_zipcode +'%'
    – Gravitai
    Feb 23, 2018 at 11:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .