Thread: Help with a selection

Hi,

I have a column named col (varchar)

col could look like this

902930

902920

902900

903110

913210

913200

…

I would like to remove an object that doesn’t end ‘00’ and if there are objects that start with the same 4 charcters and ending with 00.

All objects ending with 00 shall remain.

All object not ending with 00 shall remain of there is no object starting with the same 4 characters and ending with 00   

The result of the col table should be:

902900

903110

913200

903110 is not removed because there is no 903100

913210 is removed because there is a 913200

902930 and 902920 are removed because there is 902900

I hope you understand the logic , perhaps there is no logic in my explanation.

Thanks in advance,

Paul

Hi,

I would like to remove an object that doesn’t end ‘00’ and if there are objects that start with the same 4 charcters and ending with 00.

All objects ending with 00 shall remain.

All object not ending with 00 shall remain of there is no object starting with the same 4 characters and ending with 00   

What about:

DELETE FROM tab t1

      WHERE     right (t1.col, 2) != '00'

            AND EXISTS

                   (SELECT 1

                      FROM tab t2

                     WHERE t2.col = left (t1.col, 4) || '00');

It deletes rows where the right two characters are not ‘00’ and another row with the same first four characters and ‘00’ at the end exists.

Dirk