I have a table that looks something like this:

I need to find pairs in column id_dlry where id's numbers are the same, but the sign changes from '.' to '-'

I added another temporary column to the table, where I can identify the sign like this:

update products  set check_sign =
                (case
                when id_dlry REGEXP '[0-9]{5}[.][A-Z0-9] ' THEN '1'
                when id_dlry REGEXP '[0-9]{5}[-][A-Z0-9] ' THEN '2'
                else '0'
                end);

so it looks like this:

Now I would like to create another table only with pairs of records like "12345.67" and "12345-67", so it might look like this:

I tried using inner join but I got stuck.

CodePudding user response：

You don't need the new column. Use count(*) over(partition by replace(id_dlry, '.', '-')):

select id, id_dlry
from
(select id, 
id_dlry,
count(*) over(partition by replace(id_dlry, '.', '-')) cnt
from products) t
where cnt > 1;

Fiddle