Snowflake join using between =incorrect values

Question

I am using Snowflake, joining two tables using zip codes. One table has a zip code , other has a value linked to zip code from - zip code to combination. Zip codes go from all numbers to numbers and letters.

In SQL server the join works and returns 1 expected result.

SHIP_TO_ZIP ZIP_CODE_FROM ZIP_CODE_TO LEADTIME

40136 40121 40141 2

In Snowflake the same zip code returns multiple records

SHIP_TO_ZIP ZIP_CODE_FROM ZIP_CODE_TO LEADTIME

40136 10 79 3

40136 40121 40141 2

40136 4010 4029 4

Any idea why and how to correct this?

Dont know why in SQL server it works and in Snowflake i am having multiple records on the join

SELECT DISTINCT RR.SHIP_TO_ZIP, LT.ZIP_CODE_FROM, LT.ZIP_CODE_TO, LT.LEADTIME 
FROM
CARR RR LEFT JOIN AGREEMENTS LT 
WHERE RR.COUNTRY = LT.COUNTRY 
AND RR.SHIP_TO_ZIP BETWEEN LT.POSTCODE AND LT.POSTCODE_TO 

Thank you in advance

SOURCE DATA CARR TABLE

ship_to_zip	COUNTRY
40136	IT

AGREEMENTS TABLE

ZIP_CODE_FROM	ZIP_CODE_TO	COUNTRY	LEADTIME
10	79	IT	3
4010	4029	IT	4
40121	40141	IT	2

EXPECTED RESULT

SHIP_TO_ZIP	ZIP_CODE_FROM	ZIP_CODE_TO	LEADTIME
40136	40121	40141	2

ACTUAL RESULT

SHIP_TO_ZIP	ZIP_CODE_FROM	ZIP_CODE_TO	LEADTIME
40136	10	79	3
40136	4010	4029	4
40136	40121	40141	2

I managed workaround, where i cast in number the numerical ship_to_zip in a subquery and then join all the numerical and alphanumerical zips, but it is not eficient.

• NickW: Please don't link to images. Add all relevant information to your question as editable text, appropriately formatted
• xQbert: Are both databases using the same collation? Database collation is a set of rules that control how characters are sorted and compared in a database management system. It affects how data is stored and retrieved, particularly string values
• NickW: Also, please update your question with sample data for your source tables, the result you expect to achieve and the SQL code you are running
• NickW: Are the datatypes for the columns identical in both systems?
• Rui Sá-Dias: all columns Varchar