1

I have a list of strings in my database let say in a column

understand
understan
understa
underst
unders
under

I'm trying to find out How to delist subset strings if it is a substring of another string with sql?

So I if we pretend that this is a column of my table, the end result must be only

understand
0

If you want to delete all records except shown then try something similar to

DELETE dataset.table t1
WHERE EXISTS ( SELECT 1
               FROM dataset.table t2
               WHERE t2.word LIKE CONCAT(t1.word, '_%') )

If you want to select only shown record use

SELECT *
FROM dataset.table t1
WHERE NOT EXISTS ( SELECT 1
                   FROM dataset.table t2
                   WHERE t2.word LIKE CONCAT(t1.word, '_%') )
  • DELETE ???????? – SNaRe Nov 22 '19 at 14:50
  • @SNaRe You want that the table contains one record only in the result state - i.e. all another are to be deleted. Or you want something else? – Akina Nov 22 '19 at 15:06
  • I don't want them to be deleted but I want them to be filtered. – SNaRe Nov 22 '19 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.