0

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

afk
  • 33
  • 1
  • 5

1 Answers1

0

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for your suggestion. Unfortunately this didn't seem to work. When I used 'COLLATE(Name, 'upper')' in my query just to see how the names would appear, they still came out as lower cases. – afk Nov 20 '19 at 13:28
  • Then maybe my collation name is wrong. Can you try using `en-upper` instead? I think the suggestion to compare the two name columns using the same collation is one correct way to go here. – Tim Biegeleisen Nov 20 '19 at 13:33