Matching fields on a combination of characters

[t16turbo]

Hi,

I need to link two tables on one field.
The problem is, the value (project_no) exists in a different format on each table.

Table 1: D00151DAA
Table 2: D00151-A

I need to link the tables by looking for a match
where the first 6 characters are the same, and the 8th character.

does anyone know a fancy way of doing this?

 

[lewisp]

[tt]WHERE Substr(tab1.col1,1,6) = Substr(tab2.col1,1,6)
AND Substr(tab1.col1,8,1) = Substr(tab2.col1,8,1);[/tt]

Note that this is likely to prevent indexes from being used on either table.

 

[SantaMufasa]

T16,

Try this code:

select <expression-list>
from table_1 a, table_2 b
where substr(a.project_no,1,6)||substr(a.project_no,8,1) =
      substr(b.project_no,1,6)||substr(b.project_no,8,1)
/

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[t16turbo]

thanks guys,
thats great!