I have to join two tables using a unique field but the trouble is one table has placed that unique field in all caps and the other table has the field in all lowercase. When joining these two they bring back nothing. When I look up the data individually I can find the same records in both tables.
I am attempting to use cast or format for the field in either one or both tables. I am having troubles creating the field statement. please help
this didn't work. so I I thought I need to use cast or format the field instead.
The Standard SQL way to to a case insensitive comparison is:
ON UPPER(tab1.id) = UPPER(tab2.id)
In your case the id col was created using CASESPECIFIC (explicitly or in an ANSI mode session), check SHOW TABLE, which column is defined this way and change the join condition adding (NOT CASESPECIFIC):
ON tab1.id (NOT CASESPECIFIC) = tab2.id NOT CASESPECIFIC)
If you actually don't need this case sensitivity at all you might change it using
ALTER TABLE tab ADD id NOT CASESPECIFIC