Unanswered: Problem with Data containing different case in Join Table
Question, I need to join 2 table, and one table has data all in lower cases, while the other table has data all in upper cases. SQL statement is something like these
Select * from TABLE 1 a, TABLE 2 b where a.col_A = b.col_A and b.col_B = 'TC_DM'
Table 1 has data in lower case and Table 2 has data in upper case, and col_A on Table 1 and col_B on Table 2 does not have any referencial integrity.
I have tried something like
Select * from TABLE 1 a, TABLE 2 b where Upper(a.col_A) = b.col_A and b.col_B = 'TC_DM'
Select * from TABLE 1 a, TABLE 2 b where a.col_A = Lower(b.col_A) and b.col_B = 'TC_DM'
But I always get no rows selected.
Can anyone help me with this problem? What can I do to match the data of those 2 column so I can retrieve the right data.
Either statement should work if your statement about one being in lower case and the other uppercase. The reason it might not is maybe one has trailing spaces so do a trim around both columns in the join or one is mixed case so try doing upper aswell on both columns. If in doubt do select against each table and check the data is as expected.