We help IT Professionals succeed at work.

SQL Question - Varchar and Char type join

smagnus1
smagnus1 asked
on
Medium Priority
1,324 Views
Last Modified: 2010-04-21
Hi all.  I am attempting to perform a join in a SQL statement of varchar and char on 2 different tables based in an Oracle DB.  The field name is "Serl_Nbr" in both tables.  Granted, I'd like to correct the issue in the DB, but this is somewhat of a lost cause as I am not the DBA and do not feel comfortable generating such a change on a whim.  Thanks in advance.
Comment
Watch Question

Principal Operations Engineer
CERTIFIED EXPERT
Commented:
select ....
from tablea a
join tableb b on trim(a.Serl_Nbr) = trim(b.Serl_Nbr)
where ...
SELECT A.col, B.col FROM table1 A, table2 B WHERE TRIM(a.serl_nbr) = TRIM(b.serl_nbr);

>> I am not the DBA and do not feel comfortable generating such a change on a whim.

You need *NOT* be a DBA to make such decisions as defining a NUMERIC PK id for ur tables. If the table-design is in ur hands, then u can definitely enforce to change its structure to a more realistic design and ur DBA will be more than happy to do it for u since he realises that doing it will ultimately increase the peformance and reduce his head-ache too.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Depending on the dialect of SQL that you are using, the function you are looking for code be rtrim( ).

Cheers
  David

Author

Commented:
I truely do wish I could convince the powers that be to change this field type, but it is a "one man versus the world" type thing.  This did work, although my query is not really any faster.  I am thinking maybe a subquery, but I haven't pulled this off as of yet.  Thanks for your help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.