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.)

It's far from clear what you're trying to find here. Do you want all records that have "CDEF" anywhere in them, linked to all other records that have "DEFG" anywhere in them? Is it all records that have "CDEF" starting at position 3, linked to all other records that have "DEFG" starting at position 4? Is it all records that have characters 3 to 6 of a common string, linked to all other records that have characters 4 to 7 of the same common string? Something else? — user359040
– user359040, Commented Aug 12, 2010 at 17:10
Anywhere in them? No, just the start/end substrings I specify. Starting at position a, and b characters long... Matching records that start at position c, and are d characters long. (Of course, b and d would have to be the same for a match to occur.) — HelenJ
– HelenJ, Commented Aug 12, 2010 at 17:20

Eton B. · Accepted Answer · 2010-08-12 16:49:02Z

2

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)

answered Aug 12, 2010 at 16:49

Eton B.

6,3015 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HelenJ Over a year ago

I tried that, and it seems to return records that match themselves. Is there a way to exclude those 'same record' matches?

George Mastros Over a year ago

add another condition to the ON clause... and t1.id < t2.id

Tom H Over a year ago

Simply add AND T1.ID <> T2.ID to the WHERE clause. That's assuming that ID is unique. It would probably be best if you gave a table definition, sample data, and expected results - that's always a good idea when posting SQL query questions.

HelenJ Over a year ago

Some great ideas. Let me try them. Are either of these "better": "Add < to the ON clause"... or... "Add <> in the WHERE clause"? (To avoid records matching themselves.)

user359040 Over a year ago

@Helen: I think having it in the ON clause is better, because it makes it clear that you don't want to link to the same record. (Processing-wise, in this query it shouldn't make any difference.) Using <> would enable the same two records to appear twice with each other, if both SUBSTRING(t1.ID,3,6) = SUBSTRING(t2.ID, 4, 7) and SUBSTRING(t2.ID,3,6) = SUBSTRING(t1.ID, 4, 7) - on the other hand, if only the latter is true, then using < would mean that neither would appear. I suggest that <> is therefore better.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related