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
--
Dirk Mika
Software Developer
[cid:image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png]
mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
[cid:CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg]<https://youtu.be/qfOFXrpSKLQ>