Joining on tables where one table includes collated columns

Question

I'm trying to join two tables in Snowflake on a column called 'Name'. However, one table has names all in lowercase and the other tables has names with first character as uppercase followed by lowercase so the tables can't find a match.

Usually, you'd use an UPPER or LOWER function to find an exact match but in one table, the column has been collated and this function cannot be used here.

Does anyone have any ideas on how I can successfully join these two tables together?

Thanks

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-11-20T13:10:47

You might be able to join the two tables using the COLLATE function on the Name column of the table which was not collated:

SELECT *
FROM table1 t1
INNER JOIN table2 t2
    ON t1.Name = COLLATE(Name, 'upper');

This assumes that the first table's Name column has been collated to all uppercase.

Joining on tables where one table includes collated columns

1 Answers