SQL: Finding duplicate values in a field, but using SubString()

Question

Here's a question for all those SQL SERVER 2000 experts:

I have only 1 table... I can already find if any of the values in a certain field, also appears in another record.

I.E.: Does any record have "ABCDEFGHI" in a field, and then "ABCDEFGHI" again in that same field... but in another record.

But I run into trouble when I try to use substrings.

I.E.: Does any record have "CDEF" in a field, and then "DEFG" again in that same field... but in another record. (Edit: That would NOT be a match, of course.)

I'm trying to compare PART of 1 field, with PART of another. Only compare characters 3-6 characters, with characters 4-7. (I need to specify my own start-end ranges, for both fields.) What the specific letters are... doesn't matter. Just that they 'match'.

This doesn't seem to work:

SELECT t1.ID + ' + ' + t2.ID  
FROM InfoTable As t1         
INNER JOIN InfoTable AS t2     ON t1.ID = SUBSTRING(t2.ID, 3, 4) 

(Edit: I also need to NOT list any records that are just matching themselves.)

Answer 1

Perhaps

SELECT t1.ID + ' + ' + t2.ID  
FROM InfoTable As t1         
INNER JOIN InfoTable AS t2     ON SUBSTRING(t1.ID,3,6) = SUBSTRING(t2.ID, 4, 7)