cast or format field

General

cast or format field

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

,lcase(tbl1.id)

this didn't work. so I I thought I need to use cast or format the field instead.

2 REPLIES
Senior Apprentice

Re: cast or format field

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

Dieter

Re: cast or format field

thank you I will try your suggestions.