All Forums General
comicwizard 3 posts Joined 02/10
10 Jan 2012
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.

dnoeth 4628 posts Joined 11/04
10 Jan 2012

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

Dieter

comicwizard 3 posts Joined 02/10
10 Jan 2012

thank you I will try your suggestions.

You must sign in to leave a comment.