Tukukkk

I want a MySQL query :

To show "near" duplicate rows with : a reference and the same reference + the pattern "-??" ("-" and 2 chars ONLY, "?" is a random char).

Example with a table with id,reference :

id reference

1 DGGDL 

2 DGGDL 

3 HSDKH

4 HSDKH-45

5 2KXQF

6 2KXQF

7 2J6SF

8 2J6SF-442

9 FSM

10 148-54

11 148-54

12 148

13 BWZM-67

I want a request on this table with exactly this result :

 id reference

 3 HSDKH

 4 HSDKH-45

 10 148-54

 12 148

2J6SF-442 is not here because the pattern is "-" + 2 char only (442 is 3 char so it doesn't match the pattern).
HSDKH and HSDKH-45 are in the result because HSDKH-45 match "HSDKH-??" and HSDKH exist, BWZM-67 is NOT in the results because it match "BWZM-??" but there is no reference "BWZM" in the table.
All other "duplicates" that dont match the pattern are excluded from the result (like DGGDL because there is no reference like DGGDL-?? in the table).

my table name is products, and the simplified structure is :

id,reference

I tried many different requests without success… that's why I will not post useless request.
I don't know if I am very clear, but the example show exactly what I want.
Thank you !

I think you want:

select t.col

from t

where exists (select 1

              from t t2

              where t2.col like concat(t.col, '%-__') or

                    t1.col like concat(t2.col, '%-__')

             );

If the two characters are specifically numeric:

              where t2.col regexp concat(t.col, '-[0-9]{2}$') or

                    t1.col regexp concat(t2.col, '-[0-9]{2}$')

Or, if you want the results on one row for each group:

select group_concat(t.col)

from t

group by substring_index(t.col, '-', 1)

having sum(t.col like '%-__') > 0 and

       sum(t.col not like '%-__') > 0;

You are looking for all references that have a counterpart in the same table, where the two references only differ by the last three characters '-??'. In LIKE the character wildcard is _.

The query:

select *

from mytable t1

where exists

(

  select *

  from mytable t2

  where t1.reference like concat(t2.reference, '-__')

     or t2.reference like concat(t1.reference, '-__')



)

order by reference;

Here is another approach: Add a computed column to the table holding the reference minus the trailing '-??'. Then create an index on that column.

alter table mytable add column refshaved varchar(20) generated always as 

  (case when reference like '%-__' 

        then left(reference, length(reference)-3) 

        else reference end) stored;



create index idx on mytable(refshaved, reference);



select *

from mytable t1

where exists

(

  select *

  from mytable t2

  where t2.refshaved =  t1.refshaved

    and t2.reference <> t1.reference

)

order by reference;

Rextester demo: https://rextester.com/OLHJ35843