Custom Join

[tseh]

I have two tables that I need to join. This is a old data set so, it has not been normalized.

In table A, the field with data like "CmC0/001/001" where the last 3 characters need to be joined in table B where data is "001", for example.

How can I parse the field in the first table to join it with table B?

 

[tlbroadbent]

There are probably several ways to do this.

Select a.*, b.*
From tableA a, tableB b
Where right(a.Col1,3)=b.Col2

Alternate query:

Select
a.Col1, a.Col2, ...,
b.Col1, b.Col2, ...
From Tableb b
Inner Join
(Select Right(Col1,3), *
From TableA) a Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[rac2]

Have you tried this -

SELECT a.*, b.*
  FROM tableA a
  JOIN tableB b ON
    RIGHT(tableA_id_plus_other_stuff, 3) = b.tableB_id

 

[tseh]

Thank you! Works like a charm!