SQL like data in colums

Question

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!

Answer 1

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)