0

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the content.

For example

porn, pornography, Adult, Boobs, Booty, Tits

pseudo query may be like

SELECT * FROM videos WHERE title NOT LIKE (porn, pornography, Adult, Boobs, Booty, Tits)

I checked NOT LIKE but I have to do it like

SELECT * FROM videos WHERE title NOT LIKE ‘%porn%’ AND title NOT LIKE ‘%pornography%’ AND title NOT LIKE ‘%Adult%’ AND title NOT LIKE ‘%Booty%’ AND title NOT LIKE ‘%Boobs%’ AND title NOT LIKE ‘%Tits%’

which is very inconvenient and inefficient. So is there any other better way to do this ?

  • 1
    You should have ANDs there, not ORs. – ypercubeᵀᴹ Oct 16 '14 at 16:24
2

I've written this in SQL Server so you may have to translate to MySQL but this should work for you.

CREATE TABLE #ExcludeList (ExcludeVal varchar(50))

CREATE TABLE #VideoList (VideoName varchar(500))

INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1')

INSERT INTO #VideoList VALUES ('This is a prob1'),
            ('This is clean'),('This is also clean'),
            ('DontUse1 this one is bad')

SELECT *
FROM #VideoList VL
WHERE NOT EXISTS (SELECT 1 FROM #ExcludeList EL
                    WHERE VL.VideoName LIKE '%' + EL.ExcludeVal + '%')
|improve this answer|||||

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.