find 'duplicate'

BubikolRamios · 10-31-2011, 02:12 PM

The first would be duplicate of second. How to find those ?

Code:

Agaric g?ant
Agaric géant

Old Pedant · 10-31-2011, 07:37 PM

Make sure you are using the same collation to look at both fields??

BubikolRamios · 11-01-2011, 11:22 AM

Nope, that is all in the same field. + there can be more than one ? in string.

I was testing all kind of encoding on some text, and while some text appeared 'é' there could still be '?' inside text. No way to get them all OK.

Hence my upper question, since my insert did not violate key-s, but it is duplicate anyway.

Old Pedant · 11-01-2011, 08:09 PM

So how did the bad data (with the ?) get in there in the first place???

But anyway, couldn't you use LIKE?

Example:

Code:

SELECT t1.*
FROM table AS t1, table AS t2
WHERE t1.field LIKE REPLACE( t2.field, '?', '_' )

You replace the ? with underline, which is the one-character wildcard used by LIKE and then compare them using LIKE instead of just =